Re: How to retrieve functional index column names

["Tom Hebbron" <news_user(at)hebbron(dot)com>]

"Jakub" <glogy(at)centrum(dot)cz> wrote in message
news:c7ed2227(dot)0401052332(dot)3512fbd0(at)posting(dot)google(dot)com(dot)(dot)(dot)
> Hi,
> I need to retrieve the name of the function and the index column names
> of the functional index. The system information about the
> index(function and its args) is stored in the system catalog column
> pg_index.indexprs. Do I have to parse pg_index.indexprs text or
> pg_get_indexdef(pg_index.indexrelid) result? Am I wrong? Is there
> another way to retrieve the column names? Could anybody help me
> please.
>
> Regards Jakub

the column names are stored in pg_catalog.pg_attribute.attname - linked to
the oid in pg_class of the index.

select
c.oid::regclass,
i.*,
ia.attname
from   pg_catalog.pg_class  c
inner join  pg_catalog.pg_index  i  ON (i.indrelid = c.oid)
inner join  pg_catalog.pg_attribute ia ON (i.indexrelid = ia.attrelid);

should do the trick.


--
Tom Hebbron
www.hebbron.com