"Tom Hebbron" <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>...
> "Jakub" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
