Thread: Re: [SQL] Anyone recognise this error from PL/pgSQL?
Hi Mark, >I'm trying my first PL/pgSQL code to trap foreign key errors. Before inserting >into the table "Exon", I wish to be sure that a foreign key, 'zhvtID', >exists in the table 'zhvt'. Sounds simple... <snip code with 2 errors> >And this is the error I get when I try to insert anything, regardless of >whether >the foreign key exists or not: > >zhvt=> insert into exon (zhvtID, number, start, stop, iscomplement) >zhvt-> values (1, 1, 1, 100, 't'); >ERROR: There is no operator '=$' for types 'int4' and 'int4' > You will either have to retype this query using an explicit cast, > or you will have to define the operator using CREATE OPERATOR > Yes, I remember a posting about this a little while ago, the solution was so simple it made you want to kick yourself! SELECT * INTO zhvt_row FROM zhvt WHERE zhvtID=NEW.zhvtID; Becomes: SELECT * INTO zhvt_row FROM zhvt WHERE zhvtID = NEW.zhvtID; --spaces on either side of the equal another trick I've used in the past when getting that sort of error message is to use the function that is used by the operator directly so: SELECT * INTO zhvt_row FROM zhvt WHERE texteq(zhvtID,NEW.zhvtID); would work too. Finally, your function needs to have a RETURN in it (even though its return type is opaque) in case there is no problem with the INSERT/UPDATE. So the code becomes: CREATE FUNCTION exon_foreign_keys() RETURNS opaque AS ' DECLARE zhvt_row zhvt%ROWTYPE; BEGIN IF NEW.zhvtID ISNULL THEN RAISE EXCEPTION ''zhvtID can not be NULL''; END IF; SELECT * INTO zhvt_row FROM zhvt WHERE zhvtID = NEW.zhvtID; -- change one IF NOT FOUND THEN RAISE EXCEPTION''zhvtID= % is not in TABLE zhvt'' , NEW.zhvtID; END IF; RETURN new; -- change two END; ' LANGUAGE 'plpgsql'; (trigger code remains the same; you'll have to drop and recreate both function and the trigger though). Both of these worked under PG6.4 I'm wondering if this is a bug that should be corrected in the parser or if it is correct syntax for the operator to be bound by spaces? Regards, Stuart. +--------------------------+--------------------------------------+ | Stuart C. G. Rison | Ludwig Institute for Cancer Research | +--------------------------+ 91 Riding House Street | | N.B. new phone code!! | London, W1P 8BT | | Tel. +44 (0)207 878 4041 | UNITED KINGDOM | | Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk | +--------------------------+--------------------------------------+
Stuart Rison <stuart@ludwig.ucl.ac.uk> writes:
> Yes, I remember a posting about this a little while ago, the solution was
> so simple it made you want to kick yourself!
> SELECT * INTO zhvt_row FROM zhvt
> WHERE zhvtID=NEW.zhvtID;
> Becomes:
> SELECT * INTO zhvt_row FROM zhvt
> WHERE zhvtID = NEW.zhvtID; --spaces on either side of the equal
> I'm wondering if this is a bug that should be corrected in the parser or if
> it is correct syntax for the operator to be bound by spaces?
I thought it might be something like that ... probably plpgsql
transforms "NEW" into something involving a "$" and then the parser
misparses "=$" as a single operator. I'd argue that it's a plpgsql bug,
if that's right --- if plpgsql is doing text transformations that create
lexical ambiguities, it should be inserting spaces to prevent ambiguity.
regards, tom lane
Hi Tom, I think you are right in saying that PL/pgSQL transforms NEW into "$something" but I don't think the lexical ambiguity is in plpgsql because: CREATE FUNCTION test(int2) RETURNS int2 AS ' SELECT field2 FROM testWHERE field1=$1; ' language 'sql'; ERROR: There is no operator '=$' for types 'int2' and 'int4' You will either have to retype this query using an explicitcast, or you will have to define the operator using CREATE OPERATOR Same problem with SQL functions (this is with PG6.4). I don't know what the standard syntax for operators is but -if it is not compulsary to "bound" the operator with spaces- I guess it's a (minor) bug with the SQL parser. Cheers, Stuart. At 10:12 am -0400 16/8/99, Tom Lane wrote: >Stuart Rison <stuart@ludwig.ucl.ac.uk> writes: >> Yes, I remember a posting about this a little while ago, the solution was >> so simple it made you want to kick yourself! >> SELECT * INTO zhvt_row FROM zhvt >> WHERE zhvtID=NEW.zhvtID; >> Becomes: >> SELECT * INTO zhvt_row FROM zhvt >> WHERE zhvtID = NEW.zhvtID; --spaces on either side of the equal > >> I'm wondering if this is a bug that should be corrected in the parser or if >> it is correct syntax for the operator to be bound by spaces? > >I thought it might be something like that ... probably plpgsql >transforms "NEW" into something involving a "$" and then the parser >misparses "=$" as a single operator. I'd argue that it's a plpgsql bug, >if that's right --- if plpgsql is doing text transformations that create >lexical ambiguities, it should be inserting spaces to prevent ambiguity. > > regards, tom lane +--------------------------+--------------------------------------+ | Stuart C. G. Rison | Ludwig Institute for Cancer Research | +--------------------------+ 91 Riding House Street | | N.B. new phone code!! | London, W1P 8BT | | Tel. +44 (0)207 878 4041 | UNITED KINGDOM | | Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk | +--------------------------+--------------------------------------+
At 17:57 +0300 on 16/08/1999, Stuart Rison wrote: > CREATE FUNCTION test(int2) RETURNS int2 AS ' > SELECT field2 FROM test > WHERE field1=$1; > ' language 'sql'; > > ERROR: There is no operator '=$' for types 'int2' and 'int4' > You will either have to retype this query using an explicit cast, > or you will have to define the operator using CREATE OPERATOR > > Same problem with SQL functions (this is with PG6.4). > > I don't know what the standard syntax for operators is but -if it is not > compulsary to "bound" the operator with spaces- I guess it's a (minor) bug > with the SQL parser. It's a lexical analysis problem, not a parsing problem. When you see the string 'WHERE abcdefg=$1', do you tokenize it as <WHERE> <abcdefg> <=$> <1> or as <WHERE> <abcdefg> <=> <$1> Now, imagine someone defining the boolean operator n =$ k As meaning "n has k digits". ( 10 =$ 2 is true, 1 =$ 2 is false). Either one of the two interpretations of the WHERE clause would then be a valid one! How does one avoid a conflict? You can disallow such operators (any operator with a right-side $) - but that would limit the users and may potentially hurt existing programs. You can require that spaces are always around operators, disallowing things like field1=3 - but that would hurt even more existing pragrams. Or, you can default to one of the two interpretations. The one that expects =$ seems to be the more obvious default. Bottom line is, however, that this is a rather problematic error message. It's not much in the way of showing the user what he did wrong. Perhaps adding a line saying that "this may result from not having a space between an operator and a variable" would do the trick. And the message is completely out of touch when the $ results from an internal string replcement, in which case I really think it is recommended to add the spaces when replacing the string. It can't harm, and it will avoid the problems for sure. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Stuart Rison <stuart@ludwig.ucl.ac.uk> writes:
> CREATE FUNCTION test(int2) RETURNS int2 AS '
> SELECT field2 FROM test
> WHERE field1=$1;
> ' language 'sql';
> ERROR: There is no operator '=$' for types 'int2' and 'int4'
That case is ambiguous: is it field1 =$ 1 or field1 = $1 ? ("=$" is a
legal operator name according to Postgres.) So I don't have a problem
with disallowing that. But field1=NEW is not ambiguous under the
Postgres lexical rules, and plpgsql shouldn't be creating an
ambiguity...
regards, tom lane
At 1:35 pm -0400 16/8/99, Tom Lane wrote:
>Stuart Rison <stuart@ludwig.ucl.ac.uk> writes:
>> CREATE FUNCTION test(int2) RETURNS int2 AS '
>> SELECT field2 FROM test
>> WHERE field1=$1;
>> ' language 'sql';
>> ERROR: There is no operator '=$' for types 'int2' and 'int4'
>
>That case is ambiguous: is it field1 =$ 1 or field1 = $1 ? ("=$" is a
>legal operator name according to Postgres.) So I don't have a problem
>with disallowing that. But field1=NEW is not ambiguous under the
>Postgres lexical rules, and plpgsql shouldn't be creating an
>ambiguity...
>
> regards, tom lane
At 6:52 pm +0300 16/8/99, Herouth Maoz wrote:
>It's a lexical analysis problem, not a parsing problem. When you see the
>string 'WHERE abcdefg=$1', do you tokenize it as
><WHERE> <abcdefg> <=$> <1>
>or as
><WHERE> <abcdefg> <=> <$1>
Ah yes, I see; it's not a parser error indeed it's not a bug at all, PG is
behaving as expected with "<something>=$<something>...
I think Herouth solutions are spot on:
1) If possible PL/pgSQL should replace NEW.something in expressions like
<something>=NEW.<something> with <something>= $<whatever> (but what if it's
<something=$NEW<something> ;)
2) add the line "this may result from not having a space between
an operator and a variable" when there is a "no operator <operator> for
types <type1> and <type2>" error.
As an aside, I was trying out other operators and the following emerged:
functions=> create function test(text) returns int4 as '
functions'> SELECT 1 WHERE ''pants''::text~*$1;
functions'> ' language 'sql';
ERROR: There is no operator '~*$' for types 'text' and 'int4' You will either have to retype this query using an
explicitcast, or you will have to define the operator using CREATE OPERATOR
Is there a limit to the number of characters an operator can have?
And also:
functions=> create function test(text) returns int4 as '
functions'> SELECT 1 WHERE ''pants''::text<>?$%$1;
functions'> ' language 'sql';
ERROR: There is no operator '<>?$%$' for types 'text' and 'int4' You will either have to retype this query using
anexplicit cast, or you will have to define the operator using CREATE OPERATOR
So it looks like the operator is just considered any non-alpha/non-space
character(s) following the first variable in the clause.
If this is correct behaviour then 'right-binding' an operator with a space
should also be correct behavior (just a thought).
regards,
Stuart.
+--------------------------+--------------------------------------+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street |
| N.B. new phone code!! | London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk |
+--------------------------+--------------------------------------+
Thank you, Stuart. That does the trick and the triggers/ref-integrity now work perfectly. I was so busy looking at the error reporting code which included a "Name= %" that I never even thought to look at the comparison of the keys. Adding the spaces worked perfectly. Thanks again. Mark Stuart Rison wrote: > Hi Mark, > > >I'm trying my first PL/pgSQL code to trap foreign key errors. Before inserting > >into the table "Exon", I wish to be sure that a foreign key, 'zhvtID', > >exists in the table 'zhvt'. Sounds simple... > > <snip code with 2 errors> > > >And this is the error I get when I try to insert anything, regardless of > >whether > >the foreign key exists or not: > > > >zhvt=> insert into exon (zhvtID, number, start, stop, iscomplement) > >zhvt-> values (1, 1, 1, 100, 't'); > >ERROR: There is no operator '=$' for types 'int4' and 'int4' > > You will either have to retype this query using an explicit cast, > > or you will have to define the operator using CREATE OPERATOR > > > > Yes, I remember a posting about this a little while ago, the solution was > so simple it made you want to kick yourself! > > SELECT * INTO zhvt_row FROM zhvt > WHERE zhvtID=NEW.zhvtID; > > Becomes: > > SELECT * INTO zhvt_row FROM zhvt > WHERE zhvtID = NEW.zhvtID; --spaces on either side of the equal > > another trick I've used in the past when getting that sort of error message > is to use the function that is used by the operator directly so: > > SELECT * INTO zhvt_row FROM zhvt > WHERE texteq(zhvtID,NEW.zhvtID); > > would work too. > > Finally, your function needs to have a RETURN in it (even though its return > type is opaque) in case there is no problem with the INSERT/UPDATE. > > So the code becomes: > > CREATE FUNCTION exon_foreign_keys() RETURNS opaque AS ' > DECLARE > zhvt_row zhvt%ROWTYPE; > BEGIN > IF NEW.zhvtID ISNULL THEN > RAISE EXCEPTION ''zhvtID can not be NULL''; > END IF; > > SELECT * INTO zhvt_row FROM zhvt > WHERE zhvtID = NEW.zhvtID; -- change one > IF NOT FOUND THEN > RAISE EXCEPTION ''zhvtID= % is not in TABLE zhvt'' , NEW.zhvtID; > END IF; > RETURN new; -- change two > END; > ' LANGUAGE 'plpgsql'; > > (trigger code remains the same; you'll have to drop and recreate both > function and the trigger though). > > Both of these worked under PG6.4 > > I'm wondering if this is a bug that should be corrected in the parser or if > it is correct syntax for the operator to be bound by spaces? > > Regards, > > Stuart. > > +--------------------------+--------------------------------------+ > | Stuart C. G. Rison | Ludwig Institute for Cancer Research | > +--------------------------+ 91 Riding House Street | > | N.B. new phone code!! | London, W1P 8BT | > | Tel. +44 (0)207 878 4041 | UNITED KINGDOM | > | Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk | > +--------------------------+--------------------------------------+ -- Mark Dalphin email: mdalphin@amgen.com Mail Stop: 29-2-A phone: +1-805-447-4951 (work) One Amgen Center Drive +1-805-375-0680 (home) Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)