Thread: List of Index Columns & Expressions

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.

"David E. Wheeler" <david@justatheory.com> writes:
> 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.
        regards, tom lane

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