CREATE OR REPLACE FUNCTION fun_get_real_inv_qty(pvOrderId varchar)
RETURNS SETOF record AS
$BODY$begin
--drop table if exists tmp_1 ;
--create temp table tmp_1 as
return query
select fp_prod_id,fq_part_no,fq_name,
sum(case when fo_type='P' then -fp_qty
else 0
end
) as purchase_qty,
sum(case when fo_type='S' then -fp_qty
else 0
end
) as saleqty,
sum(case when fo_type='S' then -fp_qty
when fo_type='P' then fp_qty
else 0
end ) as surplus_qty from tp_send_det,to_send_note,tq_prod_mstr
where fp_order_id=pvOrderId and fo_note_id=fp_note_id and fq_prod_id=fp_prod_id and fq_type='I'
group by fp_prod_id,fq_part_no,fq_name ;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 10;
ALTER FUNCTION fun_get_real_inv_qty()
OWNER TO postgres;
上面是例子,调用这个函数:
select * from fun_get_real_inv_qty('D302') f(fp_prod_id bigint,fq_part_no varchar ,fq_name varchar ,purchase_qty numeric ,saleqty numeric ,surplus_qty numeric ) ;
f...后面带的是记录的column定义 必须与函数输出的列数量及每列数据类型一一对应.