一尘不染

如何在此函数中更改返回值的格式?

sql

我有一个这样创建的函数:

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)

我如何从函数中获得多列的表的收益?如何显示每个单独的列?我希望返回的结果类似于该函数内部的整个查询是从命令行正常执行的。谢谢!


阅读 204

收藏
2021-03-17

共1个答案

一尘不染

如果您的函数返回一个表,那么您应该将其像表一样对待。我们通常如何看待SQL中的表?我们从他们中选择…。您正在寻找更像这样的东西:

select ...
from nrt_summary_by_param_id(5);
2021-03-17