-- -- ec_shipment_gift_certificate/1 -- create or replace function ec_shipment_gift_certificate( integer ) returns numeric as $$ DECLARE v_shipment_id alias for $1; v_order_id ec_orders.order_id%TYPE; gift_cert_amount numeric; past_ship_amount numeric; BEGIN SELECT into v_order_id order_id FROM ec_shipments WHERE shipment_id=v_shipment_id; gift_cert_amount := ec_order_gift_cert_amount(v_order_id); SELECT into past_ship_amount coalesce(sum(ec_shipment_price(shipment_id)) + sum(ec_shipment_shipping(shipment_id))+sum(ec_shipment_tax(shipment_id)),0) FROM ec_shipments WHERE order_id = v_order_id and shipment_id <> v_shipment_id; IF past_ship_amount > gift_cert_amount THEN return 0; ELSE return least(gift_cert_amount - past_ship_amount, coalesce(ec_shipment_price(v_shipment_id) + ec_shipment_shipping(v_shipment_id) + ec_shipment_tax(v_shipment_id),0)); END IF; END;$$ language plpgsql;