在左外连接中使用返回SETOF记录的存储过程

Question

在左外连接中使用返回SETOF记录的存储过程

sqlpostgresqlstored-proceduresleft-joinlateral

8

我试图在左外连接中传递参数来调用存储过程，就像这样：

select i.name,sp.*
from items i
left join compute_prices(i.id,current_date) as sp(price numeric(15,2), 
          discount numeric(5,2), taxes numeric(5,2)) on 1=1
where i.type = 404;

compute_prices() 返回一组记录。
这是postgres显示的错误信息:

ERROR:对于表"i"的FROM子句无效引用

...left join compute_prices(i.id,current_date)...

HINT:有一个针对“i”表的条目，但不能从此查询部分引用它。

这种类型的查询在Firebird中可以使用。是否有办法只使用查询来使它工作？我不想创建另一个存储过程，循环遍历项目并进行单独调用compute_prices()。

- franbenz

1

我认为目前这是不可能的。如果我没记错，9.3版本将支持Lateral。 - user330315

我冒昧在列定义列表中添加了缺失的“numeric”类型，请确认这是否是您真正想要的。 - Erwin Brandstetter

3个回答

3

假设compute_prices函数总是返回一个包含3个价格的记录，您可以将其返回类型设置为TABLE (price numeric(15,2), discount numeric(5,2),taxes numeric(5,2))，然后我认为您想要的内容可以表达为：

SELECT i.name, (compute_prices(i.id,current_date)).*
  FROM items i
WHERE i.type=404;

请注意，对我来说，LEFT JOIN ON 1=1与未约束的普通JOIN（或CROSS JOIN）没有区别，并且我将问题解释为实际上与左连接无关。

- Daniel Vérité

1

当右表返回没有行时，LEFT JOIN ON TRUE与CROSS JOIN不同，此时LEFT JOIN结构保留左表中的行。但是，您的简化替代方案恰好做了正确的事情：“没有行”会导致NULL值。但是，看起来OP的函数可能会返回一个匿名记录（因为他提供了列定义列表）。在这种情况下，您不能使用(record).*符号。 - Erwin Brandstetter

@Erwin：左连接空表的观点很好。至于函数返回的类型，问题中指出“compute_prices sp返回一个setof record”，因此它是明确的。 - Daniel Vérité

如果函数定义为 RETURNS SETOF record（一组匿名记录），则您的查询将无法正常工作。您不能使用 (record).* 语法扩展匿名记录。它需要一个列定义列表，但我不知道如何将其适配到这种类型的查询中。我已经为此编写了一个答案。 - Erwin Brandstetter

@Erwin：我知道，这就是为什么我建议更改返回类型的原因，从我的角度来看，这才是实际问题。我的答案假设提问者选择了SETOF RECORD，但并不知道这会成为一个问题。 - Daniel Vérité

对不起，在我上一条评论中有点忽略了这一点。非常有用的答案。 - Erwin Brandstetter

1

我相信丹尼尔的答案也可以，但我还没有尝试过。我知道我有一个名为logging的模式中的名为list_failed_jobs2的SP，并且有一个名为Dual（就像在Oracle中一样）的虚拟表，以下语句适用于我：

select * from Dual left join 
              (select * from logging.list_failed_jobs2()) q on 1=1;

请注意，如果没有括号、关联（q）或ON子句，则SP调用将无法正常工作。我的SP也返回一个SETOF。

因此，我猜这样做对你有帮助：

select i.name,sp.*
from items i
left join (select * from compute_prices(i.id,current_date)) as sp on 1=1
where i.type = 404;

希望这有所帮助。

- Brian.D.Myers

我无法看出与仅使用 SELECT * FROM logging.list_failed_jobs2() 相比的好处。此外，1=1 只是在缺乏适当的 boolean 类型的数据库中（如 Oracle）使用的笨拙替代品，而在 Postgres 中则毫无意义。 - Erwin Brandstetter

我在一个包括 Oracle 等数据库的环境中工作，所以我坚持使用在任何地方都能使用的东西。 OP 想看到一个存储过程调用返回一个 SETOF 类型与一个普通表连接的示例，这个是有效的。不，这个语句本身没有实际用途；它只是为了演示语法。但我明白你的意思。我会进行编辑。 - Brian.D.Myers

不行，添加的示例不起作用。您不能在同一查询级别上的函数调用中引用 i。您需要已经提到的 LATERAL（在即将推出的版本9.3中引入）来解决这个问题。 - Erwin Brandstetter

啊，我一直在想这个问题，但从未有机会尝试过。现在我没有合适的SP可以使用。我想我已经没有更多的想法了。 - Brian.D.Myers

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

一般来说，您可以使用Daniel提供的简单语法扩展已知的行类型（也称为记录类型、复杂类型、组合类型）。其中包含了well known。

SELECT i.name, (compute_prices(i.id, current_date)).*
FROM   items i
WHERE  i.type = 404;

然而，如果您的描述是准确的……

compute_prices sp 返回一组记录。

...... 我们正在处理匿名记录。Postgres不知道如何展开匿名记录并绝望地抛出异常：

ERROR:  a column definition list is required for functions returning "record"

PostgreSQL 9.3

在PostgreSQL 9.3中有一个解决方案。如评论中所提到的，使用LATERAL：

SELECT i.name, sp.*
FROM   items i
LEFT   JOIN LATERAL compute_prices(i.id,current_date) AS sp (
                       price    numeric(15,2)
                      ,discount numeric(5,2)
                      ,taxes    numeric(5,2)
                      ) ON TRUE
WHERE i.type = 404;

细节请参考手册。

PostgreSQL 9.2及更早版本

事情变得复杂。这里有一个解决方法：编写一个包装函数，将您的匿名记录转换为已知类型。

CREATE OR REPLACE FUNCTION compute_prices_wrapper(int, date)
  RETURNS TABLE (
            price    numeric(15,2)
           ,discount numeric(5,2)
           ,taxes    numeric(5,2)
          ) AS
$func$
    SELECT * FROM compute_prices($1, $2)
    AS t(price    numeric(15,2)
        ,discount numeric(5,2)
        ,taxes    numeric(5,2));
$func$ LANGUAGE sql;

然后您可以使用@Daniel的简单解决方案，只需放入包装函数即可：

SELECT i.name, (compute_prices_wrapper(i.id, current_date)).*
FROM   items i
WHERE  i.type = 404;

PostgreSQL 8.3及以前版本

PostgreSQL 8.3已经到达生命周期终点，从现在开始不再受支持（2013年2月）。
因此，如果可能的话，最好升级。但如果不能升级：

CREATE OR REPLACE FUNCTION compute_prices_wrapper(int, date
           ,OUT price    numeric(15,2)
           ,OUT discount numeric(5,2)
           ,OUT taxes    numeric(5,2))
  RETURNS SETOF record AS
$func$
    SELECT * FROM compute_prices($1, $2)
    AS t(price    numeric(15,2)
        ,discount numeric(5,2)
        ,taxes    numeric(5,2));
$func$ LANGUAGE sql;

后续版本也适用。

正确的解决方案是修复你的函数compute_prices()，使其一开始就返回已知类型。返回SETOF record类型的函数通常很麻烦。我只会用五米长的杆子去碰这些函数。