Thread: Re: [SQL] Anyone recognise this error from PL/pgSQL?

Re: [SQL] Anyone recognise this error from PL/pgSQL?

From
Stuart Rison
Date:
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              |
+--------------------------+--------------------------------------+


Re: [SQL] Anyone recognise this error from PL/pgSQL?

From
Tom Lane
Date:
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


Re: [SQL] Anyone recognise this error from PL/pgSQL?

From
Stuart Rison
Date:
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              |
+--------------------------+--------------------------------------+


Re: [SQL] Anyone recognise this error from PL/pgSQL?

From
Herouth Maoz
Date:
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




Re: [SQL] Anyone recognise this error from PL/pgSQL?

From
Tom Lane
Date:
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


Re: [SQL] Anyone recognise this error from PL/pgSQL?

From
Stuart Rison
Date:
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              |
+--------------------------+--------------------------------------+


Re: [SQL] Anyone recognise this error from PL/pgSQL?

From
Mark Dalphin
Date:
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)