如何为旧版本的PostgreSQL重写SELECT ... CROSS JOIN LATERAL ...语句? [英] How to rewrite a SELECT ... CROSS JOIN LATERAL ... statement for older PostgreSQL versions?
问题描述
我面临着与此类似的查询:
I am faced with a query similar to this:
SELECT *
FROM invoicable_interval i,
LATERAL partition_into_months(i.start_date, i.stop_or_current_date) p;
...其中partition_into_months的定义与此类似:
... where partition_into_months is defined similar to this:
CREATE FUNCTION partition_into_months(start_date DATE, stop_date DATE)
RETURNS TABLE (start_date DATE, stop_date DATE, days INT)
AS $$ ... $$
LANGUAGE sql
IMMUTABLE;
所以我正在对辅助表进行可变间隔的交叉联接,因此(冗余)关键字LATERAL。
So I am doing a cross join with a variable interval for the secondary table, hence the (redundant) keyword LATERAL.
这在PostgreSQL 9.3.6和9.4.2上工作正常,但是在9.1.3下,我收到以下错误消息:
This works fine with PostgreSQL 9.3.6 and 9.4.2, however, with 9.1.3 I get the following error message:
[42601] ERROR: syntax error at or near "."
Position: 398
指示的位置是表达式 i.start_date中的点
The indicated position is the dot in the expression "i.start_date".
如何重写此SQL查询以便将其移植回PostgreSQL 9.1.3?
How can I rewrite this SQL query in order to port it back to PostgreSQL 9.1.3 ?
推荐答案
PostgreSQL支持在 SELECT
子句中调用集返回函数。现在我们有了 LATERAL
,这已经有些过时了,并且由于它的行为很不稳定,所以当然不建议这样做,但是它仍然有用。
PostgreSQL supports calling set-returning functions in the SELECT
clause. This is somewhat deprecated now that we have LATERAL
and is certainly discouraged because it has rather erratic behaviour, but it remains useful.
在您的情况下,您可以这样写:
In your case you could write:
SELECT
i.*,
(partition_into_months(i.start_date, i.stop_or_current_date)).*
FROM invoicable_interval i;
但是,这可能导致调用 partition_into_months $ c $由于返回
(fn)。*
基本上被宏扩展为(fn).col1,(fn).col2,.. 。
。为了避免这种情况,您可以将其包装在子查询中,例如
However, this may result in one call to partition_into_months
per column returned because (fn).*
is basically macro-expanded into (fn).col1, (fn).col2, ...
. To avoid this, you can wrap it in a subquery, e.g.
SELECT (x.i).*, (x.p).*
FROM
(
SELECT
i,
partition_into_months(i.start_date, i.stop_or_current_date) p
FROM invoicable_interval i
) x(i,p);
请注意,在<$ c $中存在多个集合返回函数时,会遇到奇怪的结果c> SELECT 列表。这不是您所期望的交叉连接。例如,比较:
Note that weird results will be encountered in the presence of multiple set returning functions in the SELECT
list. It isn't a cross-join like you would expect. For example, compare:
SELECT generate_series(1,4), generate_series(1,4)
至
SELECT generate_series(1,4), generate_series(1,3);
这篇关于如何为旧版本的PostgreSQL重写SELECT ... CROSS JOIN LATERAL ...语句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!