Re: Improving information_schema._pg_expandarray()

[ I got distracted while writing this follow-up and only just found it
  in my list of unsent Gnus buffers, and now it's probably too late to
  make it for 17, but here it is anyway while I remember. ]

Tom Lane <tgl@sss.pgh.pa.us> writes:

> I happened to notice that information_schema._pg_expandarray(),
> which has the nigh-unreadable definition
>
>     AS 'select $1[s],
>         s operator(pg_catalog.-) pg_catalog.array_lower($1,1) operator(pg_catalog.+) 1
>         from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
>                                         pg_catalog.array_upper($1,1),
>                                         1) as g(s)';
>
> can now be implemented using unnest():
>
>     AS 'SELECT * FROM pg_catalog.unnest($1) WITH ORDINALITY';
>
> It seems to be slightly more efficient this way, but the main point
> is to make it more readable.

I didn't spot this until it got committed, but it got me wondering what
eliminating the wrapper function completely would look like, so I
whipped up the attached.  It instead calls UNNEST() laterally in the
queries, which has the side benefit of getting rid of several
subselects, one of which was particularly confusing.  In one place the
lateral form eliminated the need for WITH ORDINALITY as well.


- ilmari