"Tom Hebbron" <news_user@hebbron.com> wrote in message news:<bterm0$1u8h$1@news.hub.org>...
> "Jakub" <glogy@centrum.cz> wrote in message
> news:c7ed2227.0401052332.3512fbd0@posting.google.com...
> > 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.
You are right Tom but when the index contains an expression (e.g.:
Create index "index1" on "Entity1" using btree (lower("a"));) there is
a "pg_expression_x" text stored in the pg_attribute.attname linked to
the oid in pg_class of the index. The only way I see is to parse the
pg_index.indexprs text to get the column numbers of the related table.
The pg_get_indexdef() function returns the whole expression lower("a")
but I want to retrieve the list of column names only.
Anyways thank for your comment.
Jakub