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?

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) - information is missing in the question.
Also assuming that your function always returns the same id it is fed (which is redundant in this case 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 leads to evaluation of the function once per 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: multiple evaluation.

To be precise, the equivalent of the solution in pg 9.3+ is:

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

Preserving rows in the result where the function returns 0 rows.

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 answer:

• Record returned from function has columns concatenated