Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA39804 for ; Mon, 16 Aug 1999 05:03:04 -0400 (EDT) (envelope-from stuart@ludwig.ucl.ac.uk) Received: from kestrel.ludwig.ucl.ac.uk by mail-b.bcc.ac.uk with SMTP (XT-PP); Mon, 16 Aug 1999 10:01:29 +0100 Received: from [128.40.242.190] by kestrel.ludwig.ucl.ac.uk (SMI-8.6/SMI-SVR4) id KAA15916; Mon, 16 Aug 1999 10:04:03 +0100 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Sender: stuart@kestrel.ludwig.ucl.ac.uk Message-Id: Date: Mon, 16 Aug 1999 09:59:21 +0100 To: mdalphin@amgen.com, pgsql-sql@postgresql.org From: Stuart Rison Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL? 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... >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 | +--------------------------+--------------------------------------+