> pg_get_expr() in PostgreSQL 8.0.0beta3 doesn't like the parentheses
> that surround the expression. This query works for me:
>
> SELECT pg_get_expr(trim(BOTH '()' FROM i.indexprs), i.indrelid) AS expr
> FROM pg_index i
> INNER JOIN pg_class c ON i.indrelid = c.oid
> WHERE c.relname = 'art';
>
> pg_get_expr() in PostgreSQL 7.4.5 works both ways.
Unfortunately 'trim(BOTH '()' FROM i.indexprs)' doesn't help for indices
that have more than one expression.
For example:
CREATE INDEX art_art_name_idx
ON public.art
USING btree
(my_lower(art_name::text), my_lower(art_firm_name::text));
Expression tree is
({FUNCEXPR :funcid 4061872 :funcresulttype 25 :funcretset false :funcformat
0 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype 1043
:vartypmod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25
:resulttypmod -1 :relabelformat 1})} {FUNCEXPR :funcid 4061872
:funcresulttype 25 :funcretset false :funcformat 0 :args ({RELABELTYPE :arg
{VAR :varno 1 :varattno 7 :vartype 1043 :vartypmod 39 :varlevelsup 0
:varnoold 1 :varoattno 7} :resulttype 25 :resulttypmod -1 :relabelformat
1})})
In this case pg_get_expr(trim(BOTH '()' FROM i.indexprs), i.indrelid)
returns only the first expression.
Server 7.4 returns the both that are combined by 'AND'.
Isn't it planned to implement the same behavior of the function in 8.0?
Alexander
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Alexander Zhiltsov" <ayz@ems.ru>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, October 01, 2004 9:23 PM
Subject: Re: [BUGS] Bug in PostrgeSQL 8.0beta
> On Fri, Oct 01, 2004 at 02:46:33PM +0600, Alexander Zhiltsov wrote:
>
> > Executing the following query return an error 'ERROR: unrecognized node
type: 656'.
> > SELECT pg_get_expr(i.indexprs, i.indrelid) AS expr
> > FROM pg_index i
> > INNER JOIN pg_class c ON i.indrelid = c.oid
> > WHERE c.relname = 'art'
> >
> > Expression tree is
> > ({FUNCEXPR :funcid 4061872 :funcresulttype 25 :funcretset false
:funcformat 0 :args ({RELABELTYPE :arg {VAR :varno 1 :varattno 2 :vartype
1043 :vartypmod 64 :varlevelsup 0 :varnoold 1 :varoattno 2} :resulttype 25
:resulttypmod -1 :relabelformat 1})})
>
> pg_get_expr() in PostgreSQL 8.0.0beta3 doesn't like the parentheses
> that surround the expression. This query works for me:
>
> SELECT pg_get_expr(trim(BOTH '()' FROM i.indexprs), i.indrelid) AS expr
> FROM pg_index i
> INNER JOIN pg_class c ON i.indrelid = c.oid
> WHERE c.relname = 'art';
>
> pg_get_expr() in PostgreSQL 7.4.5 works both ways.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/