Re: DatabaseMetaData.getIndexInfo and function-based indexes

Kris Jurka <books(at)ejurka(dot)com> writes:
> This is what is actually stored in the pg_attribute table as the column
> name for the index. Determining what the actual expression is would
> involve decoding the pg_index.indexpr column with pg_get_expr, which could
> be done, but then some manual parsing would need to be done to split this
> into multiple columns for something like (upper(a), lower(b)). So the
> driver can figure this information out, but doesn't presently.

FYI, pg_get_indexdef makes that pretty simple in PG >= 7.4:

regression=# create table t1 (f1 text, f2 text);
CREATE TABLE
regression=# create index t1i on t1(upper(f1), lower(f2));
CREATE INDEX
regression=# select pg_get_indexdef('t1i'::regclass, 1, false);
pg_get_indexdef
-----------------
upper(f1)
(1 row)

regression=# select pg_get_indexdef('t1i'::regclass, 2, false);
pg_get_indexdef
-----------------
lower(f2)
(1 row)

and before 7.4 we don't support multicolumn functional indexes anyway.

regards, tom lane