5

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

  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/

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

3 Answers 3

12

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

select * 
from brand_hierarchy(1234)
Sign up to request clarification or add additional context in comments.

1 Comment

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.
3

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

SELECT * FROM brand_hierarchy (id)

1 Comment

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

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.