pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart Rison <stuart@ludwig.ucl.ac.uk>
To: mdalphin@amgen.com
To: pgsql-sql@postgresql.org
Cc: stuart@ludwig.ucl.ac.uk
Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL?
Date: Mon, 16 Aug 1999 09:59:21 +0100
Message-ID: <v04020a03b3dd80db8fc9@[128.40.242.190]> (raw)

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@ludwig.ucl.ac.uk              |
+--------------------------+--------------------------------------+



view thread (9+ messages)  latest in thread

Message-ID: <v04020a03b3dd80db8fc9@[128.40.242.190]>
Permalink:  ../v04020a03b3dd80db8fc9@%5B128.40.242.190%5D/
Also on:    postgresql.org/message-id/v04020a03b3dd80db8fc9@[128.40.242.190]

 · 

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: stuart@ludwig.ucl.ac.uk, mdalphin@amgen.com
  Subject: Re: [SQL] Anyone recognise this error from PL/pgSQL?
  In-Reply-To: <v04020a03b3dd80db8fc9@[128.40.242.190]>
  References: <v04020a03b3dd80db8fc9@[128.40.242.190]>

* 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