Thread: How to retrieve functional index column names

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

glogy@centrum.cz (Jakub) writes:
> 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.

As of 7.4, this is a requirement badly in need of reconsideration.
What makes you think there is any function name involved?  Consider
something likecreate index i on t ((col + 2));

Getting the column names is still a sensible operation though.  I'd
suggest looking in pg_depend to see which columns of the table the
index depends on.
        regards, tom lane

"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.


--
Tom Hebbron
www.hebbron.com

"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

> As of 7.4, this is a requirement badly in need of reconsideration.
> What makes you think there is any function name involved?  Consider
> something like
>     create index i on t ((col + 2));
> 
> Getting the column names is still a sensible operation though.  I'd
> suggest looking in pg_depend to see which columns of the table the
> index depends on.

Thank you for your help Tom!

Regards Jakub