Combine Postgres Function With Query

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

Solution 1:

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