List of Index Columns & Expressions - Mailing list pgsql-hackers

From David E. Wheeler
Subject List of Index Columns & Expressions
Date
Msg-id C8E5E473-1093-4E93-B1DB-F6A9C0052A58@justatheory.com
Whole thread Raw
Responses Re: List of Index Columns & Expressions
List pgsql-hackers
Hackers,

I'm trying to write a query to give me a list of the columns and/or expressions in an index. For example, given this
table:

david=# \d foo      Table "public.foo"Column  |   Type    | Modifiers
---------+-----------+-----------id      | integer   | bar_ids | integer[] |
Indexes:   "idx_foo_stuff" btree (id, abs(id), (bar_ids[1]))


I'd like to write a query to emit:
i |      coalesce
---+---------------------0 | id1 | abs(id)2 | bar_ids[1]

However, it looks as if I can only get multiple expressions as a single string. The query I've come up with is:
        SELECT s.i, COALESCE(a.attname, pg_catalog.pg_get_expr( x.indexprs, ct.oid ))         FROM pg_catalog.pg_index
x        JOIN pg_catalog.pg_class ct    ON ct.oid = x.indrelid         JOIN pg_catalog.pg_class ci    ON ci.oid =
x.indexrelid        JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace         JOIN generate_series(0,
current_setting('max_index_keys')::int- 1) s(i)           ON x.indkey[s.i] IS NOT NULL         LEFT JOIN
pg_catalog.pg_attributea           ON ct.oid = a.attrelid          AND a.attnum = x.indkey[s.i]        WHERE ct.relname
='foo'          AND ci.relname = 'idx_foo_stuff'          AND n.nspname  = 'public'        ORDER BY s.i; 

Which emits:
i |      coalesce
---+---------------------0 | id1 | abs(id), bar_ids[1]2 | abs(id), bar_ids[1]

Is there some way to only get the relevant index expression from indexprs, rather than the whole expression?

Thanks,

David

PS: I need this to work all the way back to 8.1, if possible.




pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: PL/perl should fail on configure, not make
Next
From: Peter Eisentraut
Date:
Subject: Re: PL/perl should fail on configure, not make