-- -- ec_cert_unshipped_one_order/2 -- create or replace function ec_cert_unshipped_one_order( integer, integer ) returns numeric as $$ DECLARE v_gift_certificate_id alias for $1; v_order_id alias for $2; total_shipment_cost numeric; earlier_certs numeric; total_tied_amount numeric; BEGIN select coalesce(sum(coalesce(ec_shipment_price(shipment_id),0) + coalesce(ec_shipment_shipping(shipment_id),0) + coalesce(ec_shipment_tax(shipment_id),0)),0) into total_shipment_cost from ec_shipments where order_id=v_order_id; earlier_certs := ec_earlier_certs_on_one_order(v_gift_certificate_id, v_order_id); IF total_shipment_cost <= earlier_certs THEN total_tied_amount := ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id); ELSE IF total_shipment_cost > earlier_certs + ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) THEN total_tied_amount := 0; ELSE total_tied_amount := ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) - (total_shipment_cost - earlier_certs); END IF; END IF; RETURN total_tied_amount; END;$$ language plpgsql;