List of Index Columns & Expressions
От | David E. Wheeler |
---|---|
Тема | List of Index Columns & Expressions |
Дата | |
Msg-id | C8E5E473-1093-4E93-B1DB-F6A9C0052A58@justatheory.com обсуждение исходный текст |
Ответы |
Re: List of Index Columns & Expressions
|
Список | 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 по дате отправления: