Re: [SQL] Anyone recognise this error from PL/pgSQL?

Thank you, Stuart. That does the trick and the triggers/ref-integrity now work
perfectly. I was so busy looking at the error reporting code which included a
"Name= %" that I never even thought to look at the comparison of the keys. Adding
the spaces worked perfectly.

Thanks again.
Mark

Stuart Rison wrote:

> 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...
>
> <snip code with 2 errors>
>
> >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(at)ludwig(dot)ucl(dot)ac(dot)uk |
> +--------------------------+--------------------------------------+

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