pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: herouth@oumail.openu.ac.il
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL?
Date: Mon, 16 Aug 1999 19:28:56 +0100
Message-ID: <v04020a02b3de02c0175d@[128.40.242.190]> (raw)
In-Reply-To: <1611.934824902@sss.pgh.pa.us>
References: <v04020a00b3ddd52e6167@[128.40.242.190]>

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



view thread (9+ messages)  latest in thread

Message-ID: <v04020a02b3de02c0175d@[128.40.242.190]>
Permalink:  ../v04020a02b3de02c0175d@%5B128.40.242.190%5D/
Also on:    postgresql.org/message-id/v04020a02b3de02c0175d@[128.40.242.190]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: stuart@ludwig.ucl.ac.uk, tgl@sss.pgh.pa.us, herouth@oumail.openu.ac.il
  Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL?
  In-Reply-To: <v04020a02b3de02c0175d@[128.40.242.190]>
  References: <v04020a00b3ddd52e6167@[128.40.242.190]> <v04020a02b3de02c0175d@[128.40.242.190]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox