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:43:45 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Index expressions: how to recreate
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

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


Wed, 02 Jul 2003 11:51:59 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Index expressions: how to recreate