Re: List of Index Columns & Expressions

On Jan 10, 2013, at 2:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> Is there some way to only get the relevant index expression from indexprs, rather than the whole expression?
>
> pg_get_indexdef() is your friend.  You really, really don't want to
> write any client-side code that inspects indexprs directly.  It'll
> break.

Ah-hah, somehow I missed that. So this:
       SELECT s.i, pg_catalog.pg_get_indexdef( ci.oid, s.i+1, false)         FROM pg_catalog.pg_index x         JOIN
pg_catalog.pg_classct    ON ct.oid = x.indrelid         JOIN pg_catalog.pg_class ci    ON ci.oid = x.indexrelid
JOINpg_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        WHERE ct.relname = 'foo'
       AND ci.relname = 'idx_foo_stuff'          AND n.nspname  = 'public'        ORDER BY s.i 

Returns:
i | pg_get_indexdef
---+-----------------0 | id1 | abs(id)2 | (bar_ids[1])

Which is perfect. Thanks!

David