From: | Mark Dalphin <mdalphin(at)amgen(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Anyone recognise this error from PL/pgSQL? |
Date: | 1999-08-14 01:05:17 |
Message-ID: | 37B4C0CD.D2F97327@amgen.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)amgen(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Howie | 1999-08-14 03:07:00 | Re: [SQL] Anyone recognise this error from PL/pgSQL? |
Previous Message | Chad Miller | 1999-08-14 00:13:00 | err: select f() from i where (f()) in (select f() from x group by j); |