Thread: pgAdmin III - bug with reverse engineered SQL with function indexes
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));
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);
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.
Regards
Donald Fraser
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. Regards, Andreas
> 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