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.

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
– Clodoaldo Neto, Commented Jan 19, 2015 at 19:25

James A. Rosen · Accepted Answer · 2025-01-22 17:13:24Z

50

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.

edited Jan 22 at 17:13

James A. Rosen

65.6k62 gold badges186 silver badges263 bronze badges

answered Jan 17, 2015 at 4:55

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gilbert Over a year ago

The Legacy way is the one that works. My PostgreSQL version is the 9.2.4, so the LATERAL JOIN doesn't works. My sql is too slow now because, as you said, the function is executed multiple times. So I think I have to find another way to build this sql. Thank you!

Craig Ringer Over a year ago

@Gilbert If you wrap the getList(...) call in another layer of subquery you can avoid the double evaluation. I think there are some examples in other prior answers. I don't have time to mock up a test case right now.

Collectives™ on Stack Overflow

Call a function for each row in select - Postgres

1 Answer 1

Correct way - `LATERAL` join

Legacy way - SRF in `SELECT`

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Correct way - LATERAL join

Legacy way - SRF in SELECT

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

Correct way - `LATERAL` join

Legacy way - SRF in `SELECT`