Thread: Index expressions: how to recreate
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
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
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