Обсуждение: Anyone recognise this error from PL/pgSQL?

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 exonFOR 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
explicitcast,       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)

On Fri, 13 Aug 1999, Mark Dalphin wrote:

> 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... 

erm, why not use refint's check_(primary|foreign)_key() functions to
handle this ?

-- referential integrity: cascade deletes from zhvt to exon based on zhvtID
create trigger zhvt_zhvtID_trigbefore delete or update on zhvtfor each rowexecute procedure check_foreign_key ('1',
'cascade','zhvtID', 'exon',
 
'zhvtID' );

-- referential integrity: exon.zhvtid must exist in zhvt
create trigger exon_zhvtid_fkbefore insert or update on exonfor each rowexecute procedure check_primary_key( 'zhvtid',
'zhvt','zhvtid' );
 

> [SNIP]

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org    
"The distance between insanity and genius is measured only by success."