JOIN on results of returned functions
I am trying to join a table and a function that 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 identifier. The query works on PostgreSQL 9.3, but the following error is displayed on 9.1 :
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 am unable to output the calculations from the function to the request.
I cannot use JOIN LATERAL , which I understand is a new feature in 9.3.
Is there a workaround for the problem?
source to share
In Postgres 9.1 :
SELECT name, (f).* -- note the parentheses!
FROM (SELECT name, calculate_payments(id) AS f FROM person) sub;
Assuming your function has the correct return type with column names (id, action, amount)
- this question is missing information.
Also assuming your function always returns the same id
one it feeds in (which is redundant in this case and could be optimized).
The same in a 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;
Given return functions in a list SELECT
result in multiple lines. But this is a non-standard and somewhat bizarre feature. The new feature LATERAL
on page 9.3+ is preferred.
You can decompose the string type in the same step:
SELECT *, (calculate_payments(p.id)).* -- parentheses!
FROM person p
But due to the weakness of the Postgres query planner, this results in the function being evaluated once per 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: multiple evaluations.
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;
Saving rows as a result when the function returns 0 rows.
If that doesn't bother you, you can simplify in pg 9.3+:
SELECT p.id, p.name, f.action, f.amount
FROM person p, calculate_payments(p.id) f;
A close answer:
source to share