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

At 1:35 pm -0400 16/8/99, Tom Lane wrote:
>Stuart Rison <stuart(at)ludwig(dot)ucl(dot)ac(dot)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 explicit cast,
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 an explicit 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(at)ludwig(dot)ucl(dot)ac(dot)uk |
+--------------------------+--------------------------------------+