我有一个这样创建的函数:
create or replace function core.nrt_summary_by_param_id(int) returns table (reason_type_id int, reason_id int, descr varchar(256), num int, pct numeric, cause_rank int) as $$ begin return query with r3 as ( //A bunch of queries ) select r3.reason_type_id, r3.reason_id, r3.desc, r3.num, r3.pct, r3.cause_rank from r3; where r3.param_id = $1 return; end $$ language plpgsql;
当我这样调用函数时:
select nrt_summary_by_param_id(5);
结果是这样的:
nrt_summary_by_param_id ---------------------------------------------------- (1,2001,"A",14,19.72,1) (1,2006,"B",9,12.68,2) (1,2202,"C",8,11.27,3) (1,2002,"D",8,11.27,3) (1,2302,"E",7,9.86,5) (1,2201,"F",4,5.63,6) (1,2206,"G",4,5.63,6) (1,2301,"H",2,2.82,8) (1,2303,"I",2,2.82,8) (1,2005,"J",2,2.82,8) (1,2004,"K",2,2.82,8) (1,2204,"L",2,2.82,8) (,,"M",7,9.87,11) (13 rows)
我如何从函数中获得多列的表的收益?如何显示每个单独的列?我希望返回的结果类似于该函数内部的整个查询是从命令行正常执行的。谢谢!
如果您的函数返回一个表,那么您应该将其像表一样对待。我们通常如何看待SQL中的表?我们从他们中选择…。您正在寻找更像这样的东西:
select ... from nrt_summary_by_param_id(5);