On Jan 10, 2013, at 2:16 PM, Tom Lane <[email protected]> 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_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
WHERE ct.relname = 'foo'
AND ci.relname = 'idx_foo_stuff'
AND n.nspname = 'public'
ORDER BY s.i
Returns:
i | pg_get_indexdef
---+-----------------
0 | id
1 | abs(id)
2 | (bar_ids[1])
Which is perfect. Thanks!
David
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
