From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
To: mdalphin@amgen.com
To: pgsql-sql@postgresql.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL?
Date: Mon, 16 Aug 1999 09:59:21 +0100
Message-ID: <v04020a03b3dd80db8fc9@[128.40.242.190]> (raw)
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 |
+--------------------------+--------------------------------------+
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, mdalphin@amgen.com
Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL?
In-Reply-To: <v04020a03b3dd80db8fc9@[128.40.242.190]>
References: <v04020a03b3dd80db8fc9@[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