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