Re: how to find index columns

"Eric B. Ridge" wrote:
> 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
>          LEFT JOIN 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend


The columns didnt show up in your query, likely due to an issue with
the regular expression.

I can of course write a script to extract the columns from a DDL chunk
of text create index propreitary code that appears to be stored in that
table.

Fundamentally everything in me screams program incorrectness, bug
inspiring, and just plain nastiness.