Re: Query to retrieve the index columns when a function is used.

On Tue, 10 Mar 2020 at 02:16, Sterpu Victor <victor(at)caido(dot)ro> wrote:
> I'm testing on Postgresql 12.1 and I have a index like this:
> "check_dates_gist" EXCLUDE USING gist (id_test1 WITH =, id_test2 WITH =, tsrange(valid_from::timestamp without time zone, valid_to::timestamp without time zone) WITH &&)
>
> When I run this query:
> "select pc.relname, pi.indisunique, pi.indisprimary, array_agg(a.attname) as attname
> FROM pg_class pc
> JOIN pg_index pi ON pc.oid = pi.indexrelid AND pc.oid IN
> (SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname='test' AND pg_class.oid=pg_index.indrelid)
> JOIN pg_attribute a ON a.attrelid = pc.oid
> GROUP BY pc.relname,pi.indisunique, pi.indisprimary;"
> I retrieve the index but there is no detail about the columns valid_from and valid_to.
>
> How can I retrieve this detail?

Those details are in the indexprs column. There's an item there for
each 0 valued indkey. It's not going to be particularly easy for you
to parse that from SQL. Internally in PostgreSQL, we have functions
that could give you that information easily, but to access those from
SQL you'd need to write something in C. The C function is named
pull_varattnos(). That will give you a Bitmapset for each of the
columns that are found. You'd need to write a set-returning function
to return those values then join that to pg_attribute.

Alternatively, it likely wouldn't be impossible to do at the SQL level
with regexp_matches(), but building an expression to reliably extract
what you want might not be an easy task. On a quick test, I see that:

select indexrelid, unnest(r.e) from pg_index i, lateral
regexp_matches(i.indexprs, 'VAR :varno 1 :varattno (\d{1,})', 'g')
r(e) where i.indexprs is not null;

does kick out the correct results for the expression indexes I have in
my database, but there may be some more complex expressions that break
it.