How to rewrite a SELECT ... CROSS JOIN LATERAL ... statement for older PostgreSQL versions?

Question

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;

... 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;

So I am doing a cross join with a variable interval for the secondary table, hence the (redundant) keyword LATERAL.

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

The indicated position is the dot in the expression "i.start_date".

How can I rewrite this SQL query in order to port it back to PostgreSQL 9.1.3 ?

Looks to me that the function can be replaced by a (aggregating) view, and/or a generate_series(). Depends on the body of the function, obviously. — joop, May 29 '15 at 11:00

score 1 · Accepted Answer · edited Jul 25 '17 at 17:36

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;

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);

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)

to

SELECT generate_series(1,4), generate_series(1,3);

The second example looks interesting, but I get '[42P01] ERROR: missing FROM-clause entry for table "i" Position: 8' — Christian Schlichtherle, May 29 '15 at 10:24
Found a fix: You need to write "SELECT (x.i).*, (x.p).* FROM ...". Would you mind updating your solution? Then I will pick it. — Christian Schlichtherle, May 29 '15 at 10:30

How to rewrite a SELECT ... CROSS JOIN LATERAL ... statement for older PostgreSQL versions?

1 Answers1

Linked