在设置返回函数结果上进行JOIN [英] JOIN on set returning function results

问题描述

我正在尝试联接返回行的表和函数:

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

此函数为每个ID返回0、1或更多行. 该查询适用于PostgreSQL 9.3，但在 9.1 上显示以下错误:

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

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

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?

推荐答案

在Postgres 9.1 :

In Postgres 9.1:

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

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

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

相同，但冗长得多:

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功能.

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

You could decompose the row type in the same step:

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

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

But due to a weakness in the Postgres query planner, this leads to evaluation of the function once per column:

• 或者您的情况:

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

  同一问题:多次评估.

  准确地说，第9.3+版中的解决方案等效于:

  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;
  

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

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

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

  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;
  

  密切相关的答案:

  • 从函数返回的记录具有串联的列

  这篇关于在设置返回函数结果上进行JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！