sql – 使用存储过程在LEFT OUTER JOIN中返回SETOF记录

我正在尝试调用存储过程在左外连接中传递参数,如下所示：

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

compute_prices()返回一组setof记录.
这是postgres显示的消息：

ERROR: invalid reference to FROM-clause entry for table “i”

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

HINT: There is an entry for table “i”,but it cannot be referenced
from this part of the query.

这种查询适用于Firebird.有没有办法可以通过使用查询使其工作？我不想创建另一个循环遍历项目的存储过程,并单独调用compute_prices().

解决方法

通常,您可以使用 simple syntax @Daniel supplied扩展众所周知的行类型(a.k.a.记录类型,复杂类型,复合类型)：

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

但是,如果您的描述准确无误……

The compute_prices sp returns a setof record.

……我们正在处理匿名记录. Postgres不知道如何扩展匿名记录并绝望地抛出一个EXCEPTION：

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

Postgresql 9.3

Postgres 9.3中有一个解决方案.如@a_horse in the comments所述,LATERAL：

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

详情请见manual.

Postgresql 9.2及更早版本

事情变得多毛了.这是一个解决方法：编写一个包装函数,将您的匿名记录转换为众所周知的类型：

CREATE OR REPLACE FUNCTION compute_prices_wrapper(int,date)
  RETURNS TABLE (
            price    numeric(15,2)
          ) AS
$func$
    SELECT * FROM compute_prices($1,$2)
    AS t(price    numeric(15,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 has just reached EOL and is unsupported as of now (Feb. 2013).
因此,如果可能的话,你最好升级.但如果你不能：

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

也适用于更高版本.

正确的解决方案是修复函数compute_prices()以返回一个众所周知的类型.返回SETOF记录的函数通常是PITA.我只戳那些五米高的杆子.

sql – 使用存储过程在LEFT OUTER JOIN中返回SETOF记录

解决方法

猜你在找的MsSQL相关文章