Re: how to find index columns

On Dec 5, 2006, at 8:43 PM, Andrew - Supernews wrote:
> On 2006-12-06, Timasmith <timasmith@hotmail.com> wrote:
>> While pg_catalog.pg_index has the create index script I otherwise  
>> cant
>> find the index columns in the information_schema.
>
> That's because there are no index columns in the information_schema.

I'm just a lonely lurker here and I never saw Timasmith's original  
post -- only your response.  Despite this sounding more like a - 
general topic, here's the view I use:

CREATE VIEW information_schema.indexes AS        SELECT  n.nspname AS schema_name,                c.relname AS
table_name,               i.relname AS index_name,                substring(pg_get_indexdef(i.oid) FROM 'USING \\\\w+?

\\\\((.+?)\\\\)') AS column_names,                x.indisunique AS is_unique,                x.indisprimary AS is_pkey
     FROM pg_index x        JOIN pg_class c ON c.oid = x.indrelid        JOIN pg_class i ON i.oid = x.indexrelid
LEFTJOIN pg_namespace n ON n.oid = c.relnamespace        WHERE c.relkind = 'r'::"char" AND i.relkind = 'i'::"char"
 ORDER BY schema_name, table_name, is_pkey desc, is_unique  
 
desc, index_name;

Sadly, I create it in the "information_schema".  It probably doesn't  
handle functional or partial indexes nicely and it is only known to  
work with PG v8.1.x.  Maybe this will inspire someone to expand upon it.

eric