Postgres自定义函数返回记录集(虚拟表结构)

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定义 必须与函数输出的列数量及每列数据类型一一对应.