如何为旧版本的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 （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屋！