在设置返回函数结果上进行JOIN

小编典典

在设置返回函数结果上进行JOIN

sql

我试图联接返回行的表和函数：

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);

该函数为每个ID返回0、1或更多行。该查询适用于PostgreSQL 9.3，但在 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

我无法将计算从函数移到查询中。
据我了解，我无法使用 JOIN LATERAL ，这是9.3中的新功能。
有没有解决此问题的方法？

阅读 231

2021-03-23

共1个答案

小编典典

在Postgres 9.1中 ：

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

假设您的函数具有定义明确的带有列名的返回类型(id, action, amount)-问题中缺少信息。
还要假设您的函数始终返回与id进给相同的函数（在这种情况下这是多余的，并且可能已经过优化）。

同样，但更为冗长：

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;

SELECT列表中的集合返回函数导致多行。但这是一个非标准且有些古怪的功能。LATERAL最好使用pg 9.3+中的新功能。

您可以在同一步骤中分解行类型：

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

但是由于Postgres查询计划器的弱点，导致每列对该函数进行一次评估：

如何在SQL查询中使用（func（））。*语法避免使用多个函数评估？

或您的情况：

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

同样的问题：多重评估。

确切地说，pg 9.3+中的解决方案的等效项是：

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

在函数返回0行的结果中保留行。

如果您对此不关心，则可以在9.3+版中进行简化：

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

2021-03-23