Function call for each line in the list - Postgres

Question

I have a function called getList (date) ". This function returns me a list of elements (with multiple columns) from the date entered in the parameter.

If I call:

SELECT * FROM getList('12/31/2014'); 

It works great. It returns me a list with the date, item name and price.

Something like that:

 date item_description price ----------------------------------------------- 12/31/2014 banana 1 12/31/2014 apple 2.5 12/31/2014 coconut 3 

But I have another table with dates that I want to find.

So, I want to select all dates from this table, and for each row returned, I want my getList function to get this result:

  date item_description price ----------------------------------------------- 12/28/2014 banana 0.5 12/28/2014 apple 1.5 12/28/2014 coconut 2 12/31/2014 banana 1 12/31/2014 apple 2.5 12/31/2014 coconut 3 

I don’t know exactly how to do this. Of course, my data is not a list of fruits. This is just to make it all easier.

Thank you very much.

Answer 1

The right way - LATERAL join

The correct way to do this is with a side query (PostgreSQL 9.3 or later):

 SELECT d."date", f.item_description, f.price FROM mydates d, LATERAL getList(d."date") f; 

See the manual .

Legacy - SRF in SELECT

In older versions, you should use the PostgreSQL extension with some properties ... quirky ..., support for the set-return functions in SELECT -list. Do not use this if you do not know that you must support PostgreSQL 9.2 and later.

 SELECT d."date", (getList(d."date").* FROM mydates d; 

This can lead to multiple evaluations of the getList function, one for each output column.