从函数PostgreSQL返回多个列和行，而不是记录 [英] Return multiple columns and rows from a function PostgreSQL instead of record

问题描述

我正在在线阅读有关PostgreSQL的函数并返回结果 在此链接中:

I was reading online about function on PostgreSQL and returns results In this links:

1. SQL函数返回类型:TABLE与SETOF记录
2. 如何在Postgres sql中引用命名参数功能?
3. http://www.postgresqltutorial.com/plpgsql-function-返回表格/

1. SQL function return-type: TABLE vs SETOF records
2. How do I reference named parameters in Postgres sql functions?
3. http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

我已经编写了此函数:

create or replace function brand_hierarchy(account_value int)
  RETURNS table (topID INTEGER, accountId INTEGER, liveRowCount bigint,archiveRowCount bigint)
  AS
$BODY$
  SELECT * FROM my_client_numbers
where accountId  = coalesce($1,accountId);
$BODY$
LANGUAGE sql;

哪个工作并将结果返回到单列记录类型中. 请注意，可能会返回多于一行.

Which works and return the results in a single column Type of record. Note that might more than one row will return.

现在的响应是:

record
(1172,1172,1011,0)
(1172,1412,10,40)
.....

我想获得的结果不是记录，而是多列

I would like to get my results not as a record but as multiple columns

|---------|---------|------------|----------------|
| topID   |accountId|liveRowCount|archiveRowCount |
|---------|---------|------------|----------------|
| 1172    |1172     | 1011       |  0             |
| 1172    |1412     | 10         |  40            |

有没有一种方法可以从PostgreSQL函数返回多列

Is there a way to return multiple columns from a PostgreSQL function

推荐答案

通过此查询，我能够按预期看到它:

I was able to see it as expected with this query:

SELECT * FROM brand_hierarchy (id)

这篇关于从函数PostgreSQL返回多个列和行，而不是记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！