From: | "Eric B(dot) Ridge" <ebr(at)tcdi(dot)com> |
---|---|
To: | andrew(at)supernews(dot)com |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: how to find index columns |
Date: | 2006-12-06 02:03:55 |
Message-ID: | 4123DD36-93EB-4151-B90C-991655B5251A@tcdi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Dec 5, 2006, at 8:43 PM, Andrew - Supernews wrote:
> On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2006-12-06 02:08:37 | Re: psql possible TODO |
Previous Message | mark | 2006-12-06 02:03:18 | Re: psql possible TODO |