Re: Undocumented array_val[generate_series(...)] functionality?

On Mon, 12 Jul 2021 at 02:39, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> 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