Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA34677 for ; Mon, 16 Aug 1999 14:32:29 -0400 (EDT) (envelope-from stuart@ludwig.ucl.ac.uk) Received: from kestrel.ludwig.ucl.ac.uk by mail-b.bcc.ac.uk with SMTP (XT-PP); Mon, 16 Aug 1999 19:30:59 +0100 Received: from [128.40.242.190] by kestrel.ludwig.ucl.ac.uk (SMI-8.6/SMI-SVR4) id TAA21744; Mon, 16 Aug 1999 19:33:37 +0100 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Sender: stuart@kestrel.ludwig.ucl.ac.uk Message-Id: In-Reply-To: <1611.934824902@sss.pgh.pa.us> References: Your message of Mon, 16 Aug 1999 15:57:27 +0100 Date: Mon, 16 Aug 1999 19:28:56 +0100 To: Tom Lane , herouth@oumail.openu.ac.il From: Stuart Rison Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL? Cc: pgsql-sql@postgreSQL.org At 1:35 pm -0400 16/8/99, Tom Lane wrote: >Stuart Rison 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 > <=$> <1> >or as > <=> <$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 "=$... I think Herouth solutions are spot on: 1) If possible PL/pgSQL should replace NEW.something in expressions like =NEW. with = $ (but what if it's ;) 2) add the line "this may result from not having a space between an operator and a variable" when there is a "no operator for types and " 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@ludwig.ucl.ac.uk | +--------------------------+--------------------------------------+