pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mark Dalphin <mdalphin@amgen.com>
To: pgsql-sql@postgresql.org
Subject: Anyone recognise this error from PL/pgSQL?
Date: Fri, 13 Aug 1999 18:05:17 -0700
Message-ID: <37B4C0CD.D2F97327@amgen.com> (raw)

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)






view thread (9+ messages)  latest in thread

Message-ID: <37B4C0CD.D2F97327@amgen.com>
Permalink:  ../37B4C0CD.D2F97327@amgen.com/
Also on:    postgresql.org/message-id/37B4C0CD.D2F97327@amgen.com

 · 

reply

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: mdalphin@amgen.com
  Subject: Re: Anyone recognise this error from PL/pgSQL?
  In-Reply-To: <37B4C0CD.D2F97327@amgen.com>
  References: <37B4C0CD.D2F97327@amgen.com>

* 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