I'm trying to call a stored procedure passing parameters in a left outer join like this:
我正在尝试调用存储过程在左外连接中传递参数,如下所示:
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() returns a setof record.
This is the message postgres shows:
compute_prices()返回一组setof记录。这是postgres显示的消息:
ERROR: invalid reference to FROM-clause entry for table "i"
错误:对表“i”的FROM子句条目的无效引用
...left join compute_prices(i.id,current_date)...
...左连接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.
提示:表“i”有一个条目,但不能从查询的这一部分引用它。
This kind of query works in Firebird. Is there a way I could make it work by just using a query? I don't want to create another stored procedure that cycles through items and makes separate calls to compute_prices().
这种查询适用于Firebird。有没有办法可以通过使用查询使其工作?我不想创建另一个循环遍历项目的存储过程,并单独调用compute_prices()。
3 个解决方案
#1
5
Generally, you can expand well known row types (a.k.a. record type, complex type, composite type) with the simple syntax @Daniel supplied:
通常,您可以使用提供的简单语法@Daniel扩展众所周知的行类型(a.k.a。记录类型,复杂类型,复合类型):
SELECT i.name, (compute_prices(i.id, current_date)).*
FROM items i
WHERE i.type = 404;
However, if your description is accurate ...
但是,如果您的描述准确无误......
The compute_prices sp returns a setof record.
compute_prices sp返回一组记录。
... we are dealing with anonymous records. Postgres does not know how to expand anonymous records and throws an EXCEPTION in despair:
......我们正在处理匿名记录。 Postgres不知道如何扩展匿名记录并在绝望中抛出一个EXCEPTION:
ERROR: a column definition list is required for functions returning "record"
PostgreSQL 9.3
There is a solution for that in Postgres 9.3. LATERAL, as mentioned by @a_horse in the comments:
Postgres 9.3中有一个解决方案。正如@a_horse在评论中提到的那样: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;
Details in the manual.
手册中的详细信息。
PostgreSQL 9.2 and earlier
Things get hairy. Here's a workaround: write a wrapper function that converts your anonymous records into a well known type:
事情变得多毛了。这是一个解决方法:编写一个包装函数,将您的匿名记录转换为众所周知的类型:
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;
Then you can use the simple solution by @Daniel and just drop in the wrapper function:
然后你可以使用@Daniel的简单解决方案,只需输入包装函数:
SELECT i.name, (compute_prices_wrapper(i.id, current_date)).*
FROM items i
WHERE i.type = 404;
PostgreSQL 8.3 and earlier
PostgreSQL 8.3 has just reached EOL and is unsupported as of now (Feb. 2013).
So you'd better upgrade if at all possible. But if you can't:
PostgreSQL 8.3刚刚达到EOL,截至目前(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;
Works in later versions, too.
也适用于更高版本。
The proper solution would be to fix your function compute_prices() to return a well know type to begin with. Functions returning SETOF record are generally a PITA. I only poke those with a five-meter-pole.
正确的解决方案是修复函数compute_prices()以返回一个众所周知的类型。返回SETOF记录的函数通常是PITA。我只戳那些五米高的杆子。
#2
3
Assuming the compute_prices function always return a record with 3 prices, you could make its return type to TABLE (price numeric(15,2), discount numeric(5,2),taxes numeric(5,2)), and then I believe what you want could be expressed as:
假设compute_prices函数总是返回一个有3个价格的记录,你可以将它的返回类型设为TABLE(价格数字(15,2),折扣数字(5,2),税数字(5,2)),然后我相信你想要什么可以表达为:
SELECT i.name, (compute_prices(i.id,current_date)).*
FROM items i
WHERE i.type=404;
Note that its seems to me that LEFT JOIN ON 1=1 does not differ from an unconstrained normal JOIN (or CROSS JOIN), and I interpreted the question as actually unrelated to the left join.
请注意,在我看来,LEFT JOIN ON 1 = 1与无约束的正常JOIN(或CROSS JOIN)没有区别,我将该问题解释为与左连接实际上无关。
#3
1
I believe Daniel's answer will work also but haven't tried it yet. I do know that I have an SP called list_failed_jobs2 in a schema called logging, and a dummy table called Dual (like in Oracle) and the following statement works for me:
我相信丹尼尔的回答也会奏效但尚未尝试过。我知道我在名为logging的模式中有一个名为list_failed_jobs2的SP,以及一个名为Dual的虚拟表(如在Oracle中),以下语句适用于我:
select * from Dual left join
(select * from logging.list_failed_jobs2()) q on 1=1;
Note, the SP call will not work without the parens, the correlation (q), or the ON clause. My SP returns a SETOF also.
注意,没有parens,相关(q)或ON子句,SP调用将不起作用。我的SP也返回SETOF。
Thus, I suspect something like this will work for you:
因此,我怀疑这样的事情对你有用:
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;
Hope that helps.
希望有所帮助。
#1
5
Generally, you can expand well known row types (a.k.a. record type, complex type, composite type) with the simple syntax @Daniel supplied:
通常,您可以使用提供的简单语法@Daniel扩展众所周知的行类型(a.k.a。记录类型,复杂类型,复合类型):
SELECT i.name, (compute_prices(i.id, current_date)).*
FROM items i
WHERE i.type = 404;
However, if your description is accurate ...
但是,如果您的描述准确无误......
The compute_prices sp returns a setof record.
compute_prices sp返回一组记录。
... we are dealing with anonymous records. Postgres does not know how to expand anonymous records and throws an EXCEPTION in despair:
......我们正在处理匿名记录。 Postgres不知道如何扩展匿名记录并在绝望中抛出一个EXCEPTION:
ERROR: a column definition list is required for functions returning "record"
PostgreSQL 9.3
There is a solution for that in Postgres 9.3. LATERAL, as mentioned by @a_horse in the comments:
Postgres 9.3中有一个解决方案。正如@a_horse在评论中提到的那样: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;
Details in the manual.
手册中的详细信息。
PostgreSQL 9.2 and earlier
Things get hairy. Here's a workaround: write a wrapper function that converts your anonymous records into a well known type:
事情变得多毛了。这是一个解决方法:编写一个包装函数,将您的匿名记录转换为众所周知的类型:
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;
Then you can use the simple solution by @Daniel and just drop in the wrapper function:
然后你可以使用@Daniel的简单解决方案,只需输入包装函数:
SELECT i.name, (compute_prices_wrapper(i.id, current_date)).*
FROM items i
WHERE i.type = 404;
PostgreSQL 8.3 and earlier
PostgreSQL 8.3 has just reached EOL and is unsupported as of now (Feb. 2013).
So you'd better upgrade if at all possible. But if you can't:
PostgreSQL 8.3刚刚达到EOL,截至目前(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;
Works in later versions, too.
也适用于更高版本。
The proper solution would be to fix your function compute_prices() to return a well know type to begin with. Functions returning SETOF record are generally a PITA. I only poke those with a five-meter-pole.
正确的解决方案是修复函数compute_prices()以返回一个众所周知的类型。返回SETOF记录的函数通常是PITA。我只戳那些五米高的杆子。
#2
3
Assuming the compute_prices function always return a record with 3 prices, you could make its return type to TABLE (price numeric(15,2), discount numeric(5,2),taxes numeric(5,2)), and then I believe what you want could be expressed as:
假设compute_prices函数总是返回一个有3个价格的记录,你可以将它的返回类型设为TABLE(价格数字(15,2),折扣数字(5,2),税数字(5,2)),然后我相信你想要什么可以表达为:
SELECT i.name, (compute_prices(i.id,current_date)).*
FROM items i
WHERE i.type=404;
Note that its seems to me that LEFT JOIN ON 1=1 does not differ from an unconstrained normal JOIN (or CROSS JOIN), and I interpreted the question as actually unrelated to the left join.
请注意,在我看来,LEFT JOIN ON 1 = 1与无约束的正常JOIN(或CROSS JOIN)没有区别,我将该问题解释为与左连接实际上无关。
#3
1
I believe Daniel's answer will work also but haven't tried it yet. I do know that I have an SP called list_failed_jobs2 in a schema called logging, and a dummy table called Dual (like in Oracle) and the following statement works for me:
我相信丹尼尔的回答也会奏效但尚未尝试过。我知道我在名为logging的模式中有一个名为list_failed_jobs2的SP,以及一个名为Dual的虚拟表(如在Oracle中),以下语句适用于我:
select * from Dual left join
(select * from logging.list_failed_jobs2()) q on 1=1;
Note, the SP call will not work without the parens, the correlation (q), or the ON clause. My SP returns a SETOF also.
注意,没有parens,相关(q)或ON子句,SP调用将不起作用。我的SP也返回SETOF。
Thus, I suspect something like this will work for you:
因此,我怀疑这样的事情对你有用:
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;
Hope that helps.
希望有所帮助。