27

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.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
Gilbert
  • 443
  • 1
  • 4
  • 11

1 Answers1

46

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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! – Gilbert Jan 19 '15 at 14:58
  • @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. – Craig Ringer Jan 20 '15 at 04:28