-- -- ec_earlier_certs_on_one_order/2 -- create or replace function ec_earlier_certs_on_one_order( integer, integer ) returns numeric as $$ DECLARE v_gift_certificate_id alias for $1; v_order_id alias for $2; bal_amount_used numeric; BEGIN select coalesce(sum(u.amount_used),0)-coalesce(sum(u.amount_reinstated),0) into bal_amount_used from ec_gift_certificate_usage u, ec_gift_certificates g, ec_gift_certificates g2 where u.gift_certificate_id=g.gift_certificate_id and g2.gift_certificate_id=v_gift_certificate_id and u.order_id=v_order_id and (g.expires < g2.expires or (g.expires = g2.expires and g.gift_certificate_id < g2.gift_certificate_id)); return bal_amount_used; END;$$ language plpgsql;