Combine postgres function with query
I am currently struggling with the sql function outputs that I require in my result set:
SELECT getAdditionalInfoAboutDate(date) from sampleCalendar
The problem is that I am getting the result like this:
- "Attribute1, Attribute2, Attribute3"
- "Attribute2, Attribute3, Attribute4"
- ...
As you can see I am getting the desired result but I only have one column even though my function is returning 3 columns.
When I try to create a statement like:
SELECT (Select * from getAdditionalInfoAboutDate(date)) from sampleCalendar
I am getting a "subquery should only return one column" exception.
Do I have a chance to solve this somehow?
Edit: Found an answer HERE which:
SELECT getAdditionalInfoAboutDate(date).* from sampleCalendar
source to share
Even if the answer you found is from me, there might be a better answer depending on what your function actually returns: one or more lines? Assuming the line one
.
In any case, what you have in your question is wrong. The string type must be enclosed in parentheses to expand it , otherwise the syntax may be ambiguous. It should be:
SELECT (getAdditionalInfoAboutDate(date)).* FROM sampleCalendar;
However, Postgres has a weak point here. It will evaluate the function multiple times - once for each column in the result. To optimize performance , place the function call in a subquery:
SELECT (f_row).*
FROM (SELECT getAdditionalInfoAboutDate(date) AS f_row FROM sampleCalendar) sub;
Or use in page 9.3+ LEFT JOIN LATERAL
SELECT f_row.* -- no parentheses here, it a table alias
FROM sampleCalendar s
LEFT JOIN LATERAL getAdditionalInfoAboutDate(s.date) f_row ON TRUE
More details:
source to share