Re: Improving information_schema._pg_expandarray()

so 23. 12. 2023 v 19:18 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> 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 then realized that we could also borrow unnest's infrastructure
> for rowcount estimation:
>
>     ROWS 100 SUPPORT pg_catalog.array_unnest_support
>
> because array_unnest_support just looks at the array argument and
> doesn't have any hard dependency on the function being specifically
> unnest().  I'm not sure that any of its uses in information_schema
> can benefit from that right now, but surely it can't hurt.
>
> One minor annoyance is that psql.sql is using _pg_expandarray
> as a test case for \sf[+].  While we could keep doing so, I think
> the main point of that test case is to exercise \sf+'s line
> numbering ability, so the new one-line body is not a great test.
> I changed that test to use _pg_index_position instead.
>

+1

regards

Pavel


>                         regards, tom lane
>
>