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

Consider an index that looks like this:

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

Looking at pg_index:

    indkey will contain 1 0 4
    indclass contains 1988 1978 1978 1978 (numeric, int, int, int)
    pg_get_expr(indexprs, indrelid) will deliver (length((txtcol)::text) 
AND (length(((txtcol2)::text)))

indclass contains what I'd expect, but indkey shows only 3 columns 
and/or expressions.
So I'd recreate the index as being defined as

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

which obviously isn't correct (and wouldn't execute either, AND with int 
operands)

Why is indexprs not a text array containing "", "length(txtcol)", "", 
"length(txtcol2)" ?


Regards,
Andreas


Tue, 01 Jul 2003 14:08:53 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
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

Tue, 01 Jul 2003 11:00:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Index expressions: how to recreate 
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


Tue, 01 Jul 2003 23:38:34 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: [HACKERS] Index expressions: how to recreate