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:

1. "Attribute1, Attribute2, Attribute3"
2. "Attribute2, Attribute3, Attribute4"
3. ...

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

Answer 1

I wrote the answer you found, but there may be a better answer depending on what your function actually returns: one or multiple rows? Assuming one row.

What you have in the question is incorrect either way. The row expression must be enclosed in parentheses to decompose it, else the syntax is 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