Re: postgresql how to duplicate rows in result.

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)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