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 | id
1 | 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_attribute a
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 | id
1 | 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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers