JOIN on set returning function results

Question

I am trying to join table and function which returns rows:

SELECT p.id, p.name, f.action, f.amount
FROM person p
JOIN calculate_payments(p.id) f(id, action, amount) ON (f.id = p.id);

This function returns 0, 1 or more rows for each id. The query works on PostgreSQL 9.3, but on 9.1 it shows following error:

ERROR:  invalid reference to FROM-clause entry for table "p"
HINT:  There is an entry for table "p", but it cannot be referenced from this part of the query

I cannot move out calculations from function into the query.
I cannot use JOIN LATERAL which is a new feature in 9.3 as I understand.
Is there any workaround to this problem?

Spiker

Answer 1

In Postgres 9.1:

SELECT name, (f).*  -- note the parentheses!
FROM  (SELECT name, calculate_payments(id) AS f FROM person) sub;

Assuming that your function has a well-defined return type with column names (id, action, amount). And that your function always returns the same id it is fed (which is redundant and might be optimized).

The same in much more verbose form:

SELECT sub.id, sub.name, (sub.f).action, (sub.f).amount  -- parentheses!
FROM  (
   SELECT p.id, p.name, calculate_payments(p.id) AS f(id, action, amount)
   FROM   person p
   ) sub;

Set-returning functions in the SELECT list result in multiple rows. But that's a non-standard and somewhat quirky feature. The new LATERAL feature in pg 9.3+ is preferable.

You could decompose the row type in the same step:

SELECT *, (calculate_payments(p.id)).*  -- parentheses!
FROM   person p

But due to a weakness in the Postgres query planner, this would evaluate the function once per result column:

• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

Or in your case:

SELECT p.id, p.name
     , (calculate_payments(p.id)).action
     , (calculate_payments(p.id)).amount
FROM   person p

Same problem: repeated evaluation.

To be precise, the equivalent of the solution in pg 9.3+ is this, preserving rows in the result where the function returns 0 rows:

SELECT p.id, p.name, f.action, f.amount
FROM   person p
LEFT   JOIN LATERAL calculate_payments(p.id) f ON true;

If you don't care about this, you can simplify in pg 9.3+:

SELECT p.id, p.name, f.action, f.amount
FROM   person p, calculate_payments(p.id) f;

Closely related:

• Record returned from function has columns concatenated

Olimpia

Thank You @Ervin I did as you suggested: SELECT p.id, p.name, sub.action, sub.amount FROM person p INNER JOIN (SELECT (calculate_payments(p1.id)).* FROM person p1) sub ON (p.id = sub.id) Something like this and it worked. – Spiker

@faskunji: Except that's not exactly what I suggested. The function is evaluated multiple times per row this way. You can have that cheaper. – Olimpia