-- -- ec_gift_certificate_balance/1 -- create or replace function ec_gift_certificate_balance( integer ) returns numeric as $$ DECLARE v_user_id alias for $1; original_amount numeric; total_amount_used numeric; -- these only look at unexpired gift certificates -- where amount_remaining_p is t, -- hence the word subset in their names BEGIN SELECT coalesce(sum(amount),0) into original_amount FROM ec_gift_certificates_approved WHERE user_id=v_user_id AND amount_remaining_p='t' AND expires > now(); SELECT coalesce(sum(u.amount_used),0) - coalesce(sum(u.amount_reinstated),0) into total_amount_used FROM ec_gift_certificates_approved c, ec_gift_certificate_usage u WHERE c.gift_certificate_id=u.gift_certificate_id AND c.user_id=v_user_id AND c.amount_remaining_p='t' AND c.expires > now(); RETURN original_amount - total_amount_used; END;$$ language plpgsql;