Re: [GENERAL] postgresql how to duplicate rows in result. - Mailing list pgsql-general

Merlin Moncure <mmoncure@gmail.com> writes:
> On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> If it wasn't lateral the reference to number in "generate_series(1, number)"
>> would fail.

> huh -- I didn't know that!  Testing it out, all JOIN types imply
> LATERAL if the function call is tlist SRF style (which is pretty werid
> IMO)

Quite.  We wouldn't have done that, except we concluded that the SQL
spec requires it.  I don't recall all the details, but I think there
are specific function-like syntaxes (maybe only UNNEST?) that the spec
says act as if they're always LATERAL.  It seemed like it was less of
a wart to make that true for all function-in-FROM cases than just for
some of them.

> I tend to avoid optional words (with the important exception of
> AS for column list renames) but I think it's a good idea to disclose
> LATERAL in this case.

Agreed.  We do make rule printing show LATERAL explicitly when it's
active:

regression=# create view vv as select * from int8_tbl cross join generate_series(1,q1) g;
CREATE VIEW
regression=# \d+ vv
                             View "public.vv"
 Column |  Type  | Collation | Nullable | Default | Storage | Description
--------+--------+-----------+----------+---------+---------+-------------
 q1     | bigint |           |          |         | plain   |
 q2     | bigint |           |          |         | plain   |
 g      | bigint |           |          |         | plain   |
View definition:
 SELECT int8_tbl.q1,
    int8_tbl.q2,
    g.g
   FROM int8_tbl
     CROSS JOIN LATERAL generate_series(1::bigint, int8_tbl.q1) g(g);


            regards, tom lane