Received: from castle-smtp.amgen.com (firewall-user@ns2.amgen.com [138.133.17.7]) by hub.org (8.9.3/8.9.3) with ESMTP id VAA83308 for ; Fri, 13 Aug 1999 21:04:46 -0400 (EDT) (envelope-from mdalphin@amgen.com) Received: by castle-smtp.amgen.com; id SAA15880; Fri, 13 Aug 1999 18:04:45 -0700 (PDT) Received: from mailbag.amgen.com(138.133.10.78) by castle-smtp.amgen.com via smap (3.2) id xma015865; Fri, 13 Aug 99 18:04:32 -0700 Received: from maat.amgen.com (maat [138.133.145.25]) by mailbag.amgen.com (8.8.5/8.8.5) with SMTP id SAA15872 for ; Fri, 13 Aug 1999 18:01:31 -0700 (PDT) Received: from amgen.com by maat.amgen.com (SMI-8.6/SMI-SVR4) id SAA08476; Fri, 13 Aug 1999 18:04:31 -0700 Sender: mdalphin@amgen.com Message-ID: <37B4C0CD.D2F97327@amgen.com> Date: Fri, 13 Aug 1999 18:05:17 -0700 From: Mark Dalphin Organization: Amgen, Inc. X-Mailer: Mozilla 4.6C-SGI [en] (X11; U; IRIX64 6.5 IP30) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Anyone recognise this error from PL/pgSQL? Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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)