Re: Improving information_schema._pg_expandarray() - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Improving information_schema._pg_expandarray()
Date
Msg-id CAFj8pRArB19GQ9jx-wY2yA6YMqGwz0ewuueVuXtP5Oj=hFR=Rg@mail.gmail.com
Whole thread Raw
In response to Improving information_schema._pg_expandarray()  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers


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

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Improving information_schema._pg_expandarray()
Next
From: Morris de Oryx
Date:
Subject: Are operations on real values IMMUTABLE or STABLE?