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