在返回集合的函数结果上执行JOIN操作

Question

我正在尝试连接返回行的表和函数：

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

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

- faskunji

Answer 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列表中的集合返回函数会导致多行结果。但这是非标准且有些奇怪的特性。pg 9.3+ 中的新LATERAL特性更可取。

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

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+中解决方案的等效方法是保留结果中函数返回0行的行：

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

如果您不关心这个问题，您可以在pg 9.3+中简化：

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

密切相关：

• 从函数返回的记录已连接列

- Erwin Brandstetter

---

谢谢@Ervin
我按照您的建议做了：
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)
类似这样的方式，它起作用了。 - faskunji

@faskunji：除了这个，这并不是我建议的准确内容。这种方式会导致函数在每行中被多次评估。你可以更便宜地实现这个。 - Erwin Brandstetter