Call a function for each row in select - Postgres

Question

I have a function called "getList(date)". This function returns me a items list (with several columns) from the date inputted in the parameter.

If I call:

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

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

Something like this:

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 the dates that I want to search for.

So, I want to select all the dates from that table and, for each row returned, I want to call my function "getList" to have a result like this:

 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 it. Of course my data is not a fruit list. This is just to explain the whole thing easier.

Thank you very much.

Answer 1

Correct way - LATERAL join

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

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

See the manual.

Legacy way - SRF in SELECT

In older versions you must use a PostgreSQL extension with some ... quirky ... properties, support for set-returning functions in the SELECT-list. Do not use this unless you know you must support PostgreSQL 9.2 or older.

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

This may result in multiple-evaluation of the getList function, once for each column of the output.