--
-- ec_total_refund/1
--
create or replace function ec_total_refund(
  integer
) returns numeric as $$

DECLARE
	v_order_id 	alias for $1;
        v_order_refund  numeric;
        v_items_refund  numeric;
BEGIN
        select into v_order_refund 
	    coalesce(shipping_refunded,0) + coalesce(shipping_tax_refunded,0) 
	    from ec_orders where order_id=v_order_id;
        select into v_items_refund 
	    sum(coalesce(price_refunded,0)) + sum(coalesce(shipping_refunded,0)) + sum(coalesce(price_tax_refunded,0)) + sum(coalesce(shipping_tax_refunded,0)) from ec_items where order_id=v_order_id;
        return v_order_refund + v_items_refund;
END;$$ language plpgsql;