Re: [HACKERS] Index expressions: how to recreate
> 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. Have a look at the phpPgAdmin source code - we use class inheritance to take care of all that stuff quite cleanly Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index expressions: how to recreate
Andreas Pflug <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> 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 Given that that patch hasn't actually been applied yet, I think we can adjust it still. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Index expressions: how to recreate
Tom Lane wrote: Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Index expressions: how to recreate
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index expressions: how to recreate
Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Index expressions: how to recreate
Tom Lane wrote: Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Index expressions: how to recreate
Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
