如何在此函数中更改返回值的格式? [英] How do I change the formatting for my return values in this function?

问题描述

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

I have a function I created like this:

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)

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

How do I get the return from my function as a table with more than one column? How do I get each of the individual columns to show up? I would like the return to be like if the whole query that's inside the function was executed normally from the command line. Thanks!

推荐答案

如果函数返回表，则应将其视为表.我们通常如何看待SQL中的表?我们从他们中选择....您正在寻找更像这样的东西:

If your function is return a table then you should treat it like a table. How do we normally look at tables in SQL? We SELECT ... FROM them. You're looking for something more like this:

select ...
from nrt_summary_by_param_id(5);

这篇关于如何在此函数中更改返回值的格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！