pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAnyone recognise this error from PL/pgSQL?
9+ messages / 5 participants
[nested] [flat]
* Anyone recognise this error from PL/pgSQL?
@ 1999-08-14 01:05 Mark Dalphin <mdalphin@amgen.com>
0 siblings, 1 reply; 9+ messages in thread
From: Mark Dalphin @ 1999-08-14 01:05 UTC (permalink / raw)
To: pgsql-sql
Hi,
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...
This is the code I use:
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;
IF NOT FOUND THEN
RAISE EXCEPTION ''zhvtID= % is not in TABLE zhvt'' , NEW.zhvtID;
END IF;
END;
' LANGUAGE 'plpgsql';
And this is the trigger I create to call the code upon insert:
CREATE TRIGGER check_exon_FK BEFORE INSERT OR UPDATE ON exon
FOR EACH ROW EXECUTE PROCEDURE exon_foreign_keys();
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
If I drop the trigger, the error goes away.
Any ideas?
Mark
--
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Anyone recognise this error from PL/pgSQL?
@ 1999-08-14 03:07 Howie <caffeine@toodarkpark.org>
parent: Mark Dalphin <mdalphin@amgen.com>
0 siblings, 0 replies; 9+ messages in thread
From: Howie @ 1999-08-14 03:07 UTC (permalink / raw)
To: Mark Dalphin <mdalphin@amgen.com>; +Cc: pgsql-sql
On Fri, 13 Aug 1999, Mark Dalphin wrote:
> Hi,
>
> 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...
erm, why not use refint's check_(primary|foreign)_key() functions to
handle this ?
-- referential integrity: cascade deletes from zhvt to exon based on zhvtID
create trigger zhvt_zhvtID_trig
before delete or update on zhvt
for each row
execute procedure check_foreign_key ('1', 'cascade', 'zhvtID', 'exon',
'zhvtID' );
-- referential integrity: exon.zhvtid must exist in zhvt
create trigger exon_zhvtid_fk
before insert or update on exon
for each row
execute procedure check_primary_key( 'zhvtid', 'zhvt', 'zhvtid' );
> [SNIP]
---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Anyone recognise this error from PL/pgSQL?
@ 1999-08-16 08:59 Stuart Rison <stuart@ludwig.ucl.ac.uk>
0 siblings, 4 replies; 9+ messages in thread
From: Stuart Rison @ 1999-08-16 08:59 UTC (permalink / raw)
To: mdalphin@amgen.com; pgsql-sql; +Cc: stuart@ludwig.ucl.ac.uk
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 |
+--------------------------+--------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Anyone recognise this error from PL/pgSQL?
@ 1999-08-16 14:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
3 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 1999-08-16 14:12 UTC (permalink / raw)
To: Stuart Rison <stuart@ludwig.ucl.ac.uk>; +Cc: mdalphin@amgen.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Anyone recognise this error from PL/pgSQL?
@ 1999-08-16 14:57 Stuart Rison <stuart@ludwig.ucl.ac.uk>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
3 siblings, 2 replies; 9+ messages in thread
From: Stuart Rison @ 1999-08-16 14:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: mdalphin@amgen.com; pgsql-sql; jwieck@debis.com
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@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 |
+--------------------------+--------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Anyone recognise this error from PL/pgSQL?
@ 1999-08-16 15:52 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
3 siblings, 0 replies; 9+ messages in thread
From: Herouth Maoz @ 1999-08-16 15:52 UTC (permalink / raw)
To: pgsql-sql; +Cc: Stuart Rison <stuart@ludwig.ucl.ac.uk>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Anyone recognise this error from PL/pgSQL?
@ 1999-08-16 17:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 1999-08-16 17:35 UTC (permalink / raw)
To: Stuart Rison <stuart@ludwig.ucl.ac.uk>; +Cc: mdalphin@amgen.com; pgsql-sql; jwieck@debis.com
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Anyone recognise this error from PL/pgSQL?
@ 1999-08-16 18:28 Stuart Rison <stuart@ludwig.ucl.ac.uk>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
1 sibling, 0 replies; 9+ messages in thread
From: Stuart Rison @ 1999-08-16 18:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; herouth@oumail.openu.ac.il; +Cc: pgsql-sql
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 |
+--------------------------+--------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Anyone recognise this error from PL/pgSQL?
@ 1999-08-16 19:28 Mark Dalphin <mdalphin@amgen.com>
parent: Stuart Rison <stuart@ludwig.ucl.ac.uk>
3 siblings, 0 replies; 9+ messages in thread
From: Mark Dalphin @ 1999-08-16 19:28 UTC (permalink / raw)
To: Stuart Rison <stuart@ludwig.ucl.ac.uk>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~1999-08-16 19:28 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-14 01:05 Anyone recognise this error from PL/pgSQL? Mark Dalphin <mdalphin@amgen.com>
1999-08-14 03:07 ` Howie <caffeine@toodarkpark.org>
1999-08-16 08:59 Re: [SQL] Anyone recognise this error from PL/pgSQL? Stuart Rison <stuart@ludwig.ucl.ac.uk>
1999-08-16 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-16 14:57 ` Stuart Rison <stuart@ludwig.ucl.ac.uk>
1999-08-16 17:35 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-16 18:28 ` Stuart Rison <stuart@ludwig.ucl.ac.uk>
1999-08-16 15:52 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-08-16 19:28 ` Mark Dalphin <mdalphin@amgen.com>
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