Skip site navigation (1) Skip section navigation (2)

Re: pgAdmin III - bug with reverse engineered SQL with function indexes

From: "Donald Fraser" <demolish(at)cwgsy(dot)net>
To: "Andreas Pflug" <pgadmin(at)pse-consulting(dot)de>
Cc: "[pgADMIN]" <pgadmin-support(at)postgresql(dot)org>
Subject: Re: pgAdmin III - bug with reverse engineered SQL with function indexes
Date: 2003-07-31 13:27:54
Message-ID: 004001c35767$8f859450$1664a8c0@DEMOLITION (view raw or flat)
Thread:
Lists: pgadmin-support
> Donald Fraser wrote:
>
> > pgAdmin III July 16th Build
> > PostgreSQL 7.3.3
> >
> > The reverse engineered SQL for indexes that use a function do not work.
> > For example I create an index with the following command:
> > CREATE UNIQUE INDEX tbl_security_fullname_key
> >   ON tbl_security
> >   USING btree (get_securityname_4idx(s_umbname, s_name, s_classname, id));
> > pgAdmin III produces:
> > CREATE UNIQUE INDEX tbl_security_fullname_key
> >   ON public.tbl_security
> >   USING btree (public.get_securityname_4idx(s_umbname::citext,
> > s_name::citext, s_classname::citext, id::int4) citext_ops);
> > The problem exists with the appended data type on the column names as
> > the parameters of the function.
> > If I try to execute pgAdmin's version I get the following error message:
> > ERROR:  parser: parse error at or near "::" at character 220.
> >
> >
>
>
> Donald,
> that citext_ops is the operator class, which is correct according to the
> 7.3.3 doc. Additionally, the parse error is reported at "::". Please try
> to trace this down,  I don't have a 7.3 server running any more. Please
> attach a "select pg_get_viewdef(OID_of_the_Index)" output, so we can see
> what pgsql likes to see.

There's no problem with the operator class.
So going back to my original index statement: I execute:

CREATE UNIQUE INDEX tbl_security_fullname_key
  ON tbl_security
  USING btree
  (get_securityname_4idx(s_umbname, s_name, s_classname, id))

pgAdmin III generates:

CREATE UNIQUE INDEX tbl_security_fullname_key
  ON public.tbl_security
  USING btree
  (public.get_securityname_4idx(s_umbname::citext, s_name::citext,
s_classname::citext, id::int4) citext_ops);

The select pg_get_indexdef(OID_of_the_Index) produces the exact definition that
I used to create it (but without the line feeds):
CREATE UNIQUE INDEX tbl_security_fullname_key ON tbl_security USING btree
(get_securityname_4idx(s_umbname, s_name, s_classname, id))

Basically what pgAdmin III should generate for 7.3.3 is:
CREATE UNIQUE INDEX tbl_security_fullname_key
  ON public.tbl_security
  USING btree
  (public.get_securityname_4idx(s_umbname, s_name, s_classname, id)
citext_ops);

That is it needs to drop the pg_type information on the column parameters to
the function, which are in this case ::citext ... and ::int4

Regards
Donald Fraser


In response to

pgadmin-support by date

Next:From: Alex BalanDate: 2003-08-01 21:25:53
Subject: Error Message
Previous:From: Andreas PflugDate: 2003-07-31 12:36:19
Subject: Re: pgAdmin III - bug with reverse engineered SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group