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

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 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.

Cheers,

Stuart.

At 10:12 am -0400 16/8/99, Tom Lane wrote:
>Stuart Rison <stuart(at)ludwig(dot)ucl(dot)ac(dot)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(at)ludwig(dot)ucl(dot)ac(dot)uk |
+--------------------------+--------------------------------------+