pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roland Roberts <roberts@panix.com>
To: pgsql-hackers@postgreSQL.org
To: pgsql-sql@postgreSQL.org
Subject: Help with pl/pgsql, triggers, and foreign keys
Date: 28 Jan 2000 23:22:54 -0500
Message-ID: <m27lgt1p7l.fsf_-_@tycho.rlent.pnet> (raw)

-----BEGIN PGP SIGNED MESSAGE-----

The Postgres guide says that foreign keys can be partially emulated
via triggers.  Just how "partial" is this.  I can't seem to get the
following to work.  Would it work if I wrote it in C?  Would I need to
open a second connection to the database?  Would it work if my second
key was really in another table?

project=> CREATE TABLE task (
project->     task_id             INT PRIMARY KEY,
project->     task_pid            INT
project-> );
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'task_pkey' for table 'task'
CREATE
project=> CREATE FUNCTION check_task_pid () RETURNS OPAQUE AS '
project'>     BEGIN
project'>         IF NEW.task_pid IS NOT NULL THEN
project'>             SELECT task_id FROM task WHERE task_id = NEW.task_pid;
project'>             IF NOT FOUND THEN
project'>                 RAISE EXCEPTION ''FOREIGN KEY constraint task_pid not found'';
project'>             END IF;
project'>         END IF;
project'>         RETURN NEW;
project'>     END;
project'> ' LANGUAGE 'plpgsql';
CREATE
project=> CREATE TRIGGER iu_btrig_check_task_pid BEFORE INSERT OR UPDATE ON task
project->     FOR EACH ROW EXECUTE PROCEDURE check_task_pid();
CREATE
project=> insert into task values (1, null);
INSERT 27855 1
project=> insert into task values (2, null);
INSERT 27856 1
project=> insert into task values (3, 1);
ERROR:  unexpected SELECT query in exec_stmt_execsql()

roland
- -- 
                       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                  Custom Software Solutions
roberts@panix.com                      76-15 113th Street, Apt 3B
rbroberts@acm.org                          Forest Hills, NY 11375

-----BEGIN PGP SIGNATURE-----
Version: 2.6.3a
Charset: noconv
Comment: Processed by Mailcrypt 3.5.4, an Emacs/PGP interface

iQCVAwUBOJJrHeoW38lmvDvNAQHoqAP/X5oaVa3vSyBu+6WKhRMiuWVJMTN6OSDf
KMhc2kQ67qf2eULtQLe8D9YqlHg92ezHH2xGuzbDab5ha9i0vDGHLbR6Zo93EoGT
TCqriZ6xBNec4m4PgB5QQZfKRlsvrSsBgTnpnsOc6SWnKfVgNPK4If/qNYUQKmjp
3O2bwrslErE=
=+Sp8
-----END PGP SIGNATURE-----



view thread (4+ messages)  latest in thread

Message-ID: <m27lgt1p7l.fsf_-_@tycho.rlent.pnet>
Permalink:  ../m27lgt1p7l.fsf_-_@tycho.rlent.pnet/
Also on:    postgresql.org/message-id/m27lgt1p7l.fsf_-_@tycho.rlent.pnet

 · 

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: roberts@panix.com, pgsql-hackers@postgreSQL.org
  Subject: Re: Help with pl/pgsql, triggers, and foreign keys
  In-Reply-To: <m27lgt1p7l.fsf_-_@tycho.rlent.pnet>
  References: <m27lgt1p7l.fsf_-_@tycho.rlent.pnet>

* 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