Re: Changed SRF in targetlist handling

On Mon, May 23, 2016 at 4:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> We should consider single and multiple SRFs in a targetlist as distinct
> use-cases; only the latter has got weird properties.
>
> There are several things we could potentially do with multiple SRFs in
> the same targetlist.  In increasing order of backwards compatibility and
> effort required:
>
> 1. Throw error if there's more than one SRF.
>
> 2. Rewrite into LATERAL ROWS FROM (srf1(), srf2(), ...).  This would
> have the same behavior as before if the SRFs all return the same number
> of rows, and otherwise would behave differently.

I thought the idea was to rewrite it as LATERAL ROWS FROM (srf1()),
LATERAL ROWS FROM (srf2()), ...

The rewrite you propose here seems to NULL-pad rows after the first
SRF is exhausted:

rhaas=# select * from dual, lateral rows from (generate_series(1,3),
generate_series(1,4));  x   | generate_series | generate_series
-------+-----------------+-----------------dummy |               1 |               1dummy |               2 |
   2dummy |               3 |               3dummy |                 |               4
 
(4 rows)

...whereas with a separate LATERAL clause for each row you get this:

rhaas=# select * from dual, lateral rows from (generate_series(1,3))
a, lateral rows from (generate_series(1,4)) b;  x   | a | b
-------+---+---dummy | 1 | 1dummy | 1 | 2dummy | 1 | 3dummy | 1 | 4dummy | 2 | 1dummy | 2 | 2dummy | 2 | 3dummy | 2 |
4dummy| 3 | 1dummy | 3 | 2dummy | 3 | 3dummy | 3 | 4
 
(12 rows)

The latter is how I'd expect SRF-in-targetlist to work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company