Thread: DatabaseMetaData.getIndexInfo and function-based indexes

say i have an index:

create index my_index on my_Table (upper(my_field) );

it is possible to retrieve the fact that the field is upper(my_field)
via DMD.getIndexInfo() ?

With 7.4 (and the associated JDBC driver) the column name shows up as
pg_expression_1 (or something very close to that :)

... or is this something that is fixed in 8.0?

Thanks.

-pete

On Fri, 18 Feb 2005, peter royal wrote:

> create index my_index on my_Table (upper(my_field) );
>
> it is possible to retrieve the fact that the field is upper(my_field)
> via DMD.getIndexInfo() ?
>
> With 7.4 (and the associated JDBC driver) the column name shows up as
> pg_expression_1 (or something very close to that

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.

Kris Jurka

Kris Jurka <books@ejurka.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

On Feb 19, 2005, at 12:54 AM, Tom Lane wrote:
> Kris Jurka <books@ejurka.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:

i'm mainly concerned about single-column functional indexes right now.
being able to get upper(a) as the column name would be enough now. but
tom's method for working with multi-column functional indices would be
a bonus too.

would a patch to have DMD.getIndexInfo() return this information be
accepted into the tree? if so, i'll work one up.
-pete

On Sat, 19 Feb 2005, peter royal wrote:

> i'm mainly concerned about single-column functional indexes right now.
> being able to get upper(a) as the column name would be enough now. but
> tom's method for working with multi-column functional indices would be
> a bonus too.
>
> would a patch to have DMD.getIndexInfo() return this information be
> accepted into the tree? if so, i'll work one up.


Yes, a column name of "pg_expression_N" is rather useless.  upper(a) may
not be a column name, but it is more accurate and informative.

Kris Jurka

On Feb 19, 2005, at 2:27 PM, Kris Jurka wrote:
> On Sat, 19 Feb 2005, peter royal wrote:
>
>> i'm mainly concerned about single-column functional indexes right now.
>> being able to get upper(a) as the column name would be enough now. but
>> tom's method for working with multi-column functional indices would be
>> a bonus too.
>>
>> would a patch to have DMD.getIndexInfo() return this information be
>> accepted into the tree? if so, i'll work one up.
>
>
> Yes, a column name of "pg_expression_N" is rather useless.  upper(a)
> may
> not be a column name, but it is more accurate and informative.

Patch + test cases attached.
-pete

On Wed, 23 Feb 2005, peter royal wrote:

> >> would a patch to have DMD.getIndexInfo() return this information be
> >> accepted into the tree? if so, i'll work one up.
>
> Patch + test cases attached.

Looks reasonable, but you've checked the 7.4+ functionality correctly in
the metadata call, but not in the test cases.

Kris Jurka

On Wed, 23 Feb 2005, peter royal wrote:
> >> would a patch to have DMD.getIndexInfo() return this information be
> >> accepted into the tree? if so, i'll work one up.
> >
> > Yes, a column name of "pg_expression_N" is rather useless.  upper(a)
> > may not be a column name, but it is more accurate and informative.
>
> Patch + test cases attached.

I've applied this with some minor modifications:

- I've extended it to work for < 7.4 servers
- You had modified the SELECT column order to return COLUMN_NAME in
  a different position than required by the spec.
- Fixed the test cases to pass for < 7.4 servers

Thanks.

Kris Jurka