Call a function for each row in select - Postgres

21
votes

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.

1
postgresqlfunctionpostgresql-9.2
Version? select version() – Clodoaldo Neto
@ClodoaldoNeto the version is PostgreSQL 9.2.4 – Gilbert
Can you change the function, or create another, that accepts a date array as parameter? The array would be unnested and joined inside the function. It would be called like this: select * from getList((select array_agg(the_date) from dates_table)) – Clodoaldo Neto

1 Answers

29
votes

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.