-- -- gift_certificate_amount_left/1 -- create or replace function gift_certificate_amount_left( integer ) returns numeric as $$ DECLARE v_gift_certificate_id alias for $1; original_amount numeric; total_amount_used numeric; BEGIN SELECT coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0) into total_amount_used FROM ec_gift_certificate_usage WHERE gift_certificate_id = v_gift_certificate_id; SELECT amount into original_amount FROM ec_gift_certificates WHERE gift_certificate_id = v_gift_certificate_id; RETURN original_amount - total_amount_used; END;$$ language plpgsql;