Return multiple columns and rows from a function PostgreSQL instead of record

Question

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

I have written this Function:

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.

Now the response is:

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            |

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

user330315 · Accepted Answer · 2018-03-15 13:10:07Z

12

Functions returning a table (or setof) should be used in the FROM clause:

select * 
from brand_hierarchy(1234)

answered Mar 15, 2018 at 13:10

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

cander Over a year ago

Thank you a_horse_with_no_name! From this I managed to work it out. Having originally written my function in MS-SQL which automatically returned the results in columns, I was trying, like many newbies I suspect, to modify the actual function to produce the results, not thinking I had to modify how I asked the function for the information. So by adding a from to my calling statement as you have detailed above, it all works. Thank you again.

2Big2BeSmall · Accepted Answer · 2018-10-29 09:38:50Z

3

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

SELECT * FROM brand_hierarchy (id)

edited Oct 29, 2018 at 9:38

answered Mar 15, 2018 at 12:58

2Big2BeSmall

1,3683 gold badges26 silver badges44 bronze badges

1 Comment

Pavel Stehule Over a year ago

This syntax can have a performance issue. The brand_hiearchy function is called for any output column once

Mohammed · Accepted Answer · 2020-08-11 00:16:59Z

0

I found this function crosstab I think it is what you're looking for https://www.postgresql.org/docs/9.3/tablefunc.html

answered Aug 11, 2020 at 0:16

Mohammed

2,7511 gold badge12 silver badges8 bronze badges

Collectives™ on Stack Overflow

Return multiple columns and rows from a function PostgreSQL instead of record

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related