Index expressions: how to recreate

Andreas Pflug <pgadmin@pse-consulting.de> writes:

I noticed the new expression functionality of indices and while
implementing them in pgadmin3 was wonderingnow to extract the definition
from the catalog.

The best way is to use pg_get_indexdef(indexOID), same as pg_dump and
psql do.

CREATE INDEX foo ON bar (numcol, length(txtcol), intcol2,
length(txtcol2))

indkey will contain 1 0 4

Actually it should be read as 1 0 4 0. The output converter for
int2vector suppresses trailing zeroes, for largely-historical reasons.

regards, tom lane

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

I noticed the new expression functionality of indices and while
implementing them in pgadmin3 was wonderingnow to extract the definition
from the catalog.

The best way is to use pg_get_indexdef(indexOID), same as pg_dump and
psql do.

So far for the SQL window, which will show just a plain (and hopefully
nicely formatted. readable) sql query to recreate the index. Still, a
verbose display of the indexes property is not possible this way, unless
I parse the pg_get_indexdef output...

pg_get_indexdef converts that string to a list of nodes (not
surprising), while pg_get_expr whill join these list elements with an
explicit and (according to a comment, needed for partial index). Do I
need to retrieve indexprs and split it myself (counting brackets) or is
there a pg_xxx function that could help me (pg_get_element(indexprs,
0...n)) ?

Actually it should be read as 1 0 4 0. The output converter for
int2vector suppresses trailing zeroes, for largely-historical reasons.

Ok, I understand that, because indkey[n>4] will deliver 0 too, and
indnatts will show that 4 arguments are needed.

Regards,
Andreas

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

I noticed the new expression functionality of indices and while
implementing them in pgadmin3 was wonderingnow to extract the definition
from the catalog.

The best way is to use pg_get_indexdef(indexOID), same as pg_dump and
psql do.

So far for the SQL window, which will show just a plain (and hopefully
nicely formatted. readable) sql query to recreate the index. Still, a
verbose display of the indexes property is not possible this way, unless
I parse the pg_get_indexdef output...

pg_get_indexdef converts that string to a list of nodes (not
surprising), while pg_get_expr whill join these list elements with an
explicit and (according to a comment, needed for partial index). Do I
need to retrieve indexprs and split it myself (counting brackets) or is
there a pg_xxx function that could help me (pg_get_element(indexprs,
0...n)) ?

Actually it should be read as 1 0 4 0. The output converter for
int2vector suppresses trailing zeroes, for largely-historical reasons.

Ok, I understand that, because indkey[n>4] will deliver 0 too, and
indnatts will show that 4 arguments are needed.

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Tom Lane wrote:

The best way is to use pg_get_indexdef(indexOID), same as pg_dump and
psql do.

[ doesn't want to parse the pg_get_indexdef output... ]

Well, I guess if you just want one column it's kind of a pain.

pg_get_indexdef converts that string to a list of nodes (not
surprising), while pg_get_expr whill join these list elements with an
explicit and (according to a comment, needed for partial index). Do I
need to retrieve indexprs and split it myself (counting brackets) or is
there a pg_xxx function that could help me (pg_get_element(indexprs,
0...n)) ?

There isn't any real good way to do it, and I'd discourage you from
writing client-side code that roots around in those fields anyway.
It's much too likely to break in future versions.

Does anyone else think it's reasonable to define a backend function
along the lines of pg_get_indexdef(indexoid, columnnumber) that
retrieves just the column-name-or-expression for the indicated column
of the index? I'm not eager to do it if just one person wants it,
but if there's more than one potential user...

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 01 July 2003 23:26
To: Andreas Pflug
Cc: PostgreSQL Development
Subject: Re: [HACKERS] Index expressions: how to recreate

Does anyone else think it's reasonable to define a backend
function along the lines of pg_get_indexdef(indexoid,
columnnumber) that retrieves just the
column-name-or-expression for the indicated column of the
index? I'm not eager to do it if just one person wants it,
but if there's more than one potential user...

One pgAdmin developer == tens of thousands of pgAdmin users

:-)

Regards, Dave.

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

pg_get_indexdef converts that string to a list of nodes (not
surprising), while pg_get_expr whill join these list elements with an
explicit and (according to a comment, needed for partial index). Do I
need to retrieve indexprs and split it myself (counting brackets) or is
there a pg_xxx function that could help me (pg_get_element(indexprs,
0...n)) ?

There isn't any real good way to do it, and I'd discourage you from
writing client-side code that roots around in those fields anyway.
It's much too likely to break in future versions.

Clearly understood, while pgadmin3 will always behave quite backend
specific. The code for index display broke just shortly ago, because the
column pg_index.indproc went away. There's a growing number of version
specific stuff in it, because we try to prevent the user from doing
illegal stuff. A backend function is certainly the better way.

Does anyone else think it's reasonable to define a backend function
along the lines of pg_get_indexdef(indexoid, columnnumber) that
retrieves just the column-name-or-expression for the indicated column
of the index?

There's a (pre-feature-freeze) patch pending, which implements
pg_get_indexdef(oid, int4), but the second parameter's meaning is the
pretty-print option. Now I'd rather like a function
pg_get_indexdef(indexoid, coumnnumber_int2, prettyprint_int4), I could
implement this quite fast but it's post-feature-freeze....

I'm not eager to do it if just one person wants it, but if there's more than one potential user...

:-)

If it was just for my personal fun, I'd implement a module.

Regards,
Andreas