On Mon, 12 Jul 2021 at 02:39, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote: > > One, the select generate_series(1,3) function call causes multiple rows to be generated where there would usually be only one. Yes. > Two, composition results in an inside-to-outside execution order: the SRF is evaluated first, the additional rows added, then the outer function (abs or the subscript function respectively in these examples) is evaluated for whatever rows are now present in the result. Yes. > Is the above something one can learn from our documentation? Yes, but only if you know where to look. > Is this syntax we are discouraging users from using and thus intentionally not documenting it? On the contrary, I would say that this is the expected behaviour, and that it is documented, though not in the most obvious place: https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET That's probably not the first place one would go looking for it, and might also (wrongly) imply that it only works for functions written in language SQL. BTW, this is something that started working in PG10 (in 9.6, an error is thrown), and I think that it's a result of this release note item, which matches your conclusions: Change the implementation of set-returning functions appearing in a query's SELECT list (Andres Freund) Set-returning functions are now evaluated before evaluation of scalar expressions in the SELECT list, much as though they had been placed in a LATERAL FROM-clause item. This allows saner semantics for cases where multiple set-returning functions are present. If they return different numbers of rows, the shorter results are extended to match the longest result by adding nulls. Previously the results were cycled until they all terminated at the same time, producing a number of rows equal to the least common multiple of the functions' periods. In addition, set-returning functions are now disallowed within CASE and COALESCE constructs. For more information see Section 37.4.8. Another interesting consequence of that is that it's possible to do a similar thing with the array slice syntax, and a pair of generate_series() calls, for example: SELECT (array[1,2,3,4]::int[])[generate_series(1,4) : generate_series(2,4)]; array ------- {1,2} {2,3} {3,4} (4 rows) Note: there are 4 rows in that result, and the last one is NULL, which is also consistent with the documentation, and the fact that the array slice function returns NULL if either subscript is NULL. I'd agree that there's an opportunity to improve the docs here. Regards, Dean
