Combine postgres function with query

Question

I currently struggle with the output of a sql-function which I require in my result-set:

SELECT getAdditionalInfoAboutDate(date) from sampleCalendar

The problem is, that I get the result in the following way:

"Attribute1, Attribute2, Attribute3"
"Attribute2, Attribute3, Attribute4"
...

As you can see I get my desired result but it only has one column even though my function returns 3 columns.

When I try to create a statement like:

SELECT (Select * from getAdditionalInfoAboutDate(date)) from sampleCalendar

I get the exception "subquery must return only one column".

Do I have the chance to solve this somehow?

Edit: Found the answer HERE which is:

SELECT getAdditionalInfoAboutDate(date).* from sampleCalendar

How about having the function return the date too and then do `select * from getAdditionalInfoAboutDate(date)`? — Mureinik, Nov 16 '14 at 19:31
What is the type or the returned value? `record` ? Could you please give more code about your function ? — Houari, Nov 16 '14 at 20:05
nevermind, see my edited question - found the answer in a different thread! — Frame91, Nov 16 '14 at 20:05

score 1 · Accepted Answer · edited May 23 '17 at 12:24

Even though the answer you found is from me, there may be a better answer depending on what your function actually returns: one or multiple rows? Assuming one row.

Either way, what you have in your question is incorrect. The row type must be enclosed in parentheses to decompose it, else the syntax could be ambiguous. It must be:

SELECT (getAdditionalInfoAboutDate(date)).* FROM sampleCalendar;

However, Postgres has a weak spot here. It would evaluate the function multiple times - once for every column in the result. To optimize performance put the function call in a subquery:

SELECT (f_row).*
FROM  (SELECT getAdditionalInfoAboutDate(date) AS f_row FROM sampleCalendar) sub;

Or use a LEFT JOIN LATERAL in pg 9.3+

SELECT f_row.*  -- no parentheses here, it's a table alias
FROM   sampleCalendar s
LEFT   JOIN LATERAL getAdditionalInfoAboutDate(s.date) f_row ON TRUE

More details:

Record returned from function has columns concatenated

Combine postgres function with query

1 Answers1

Linked