Help with pl/pgsql, triggers, and foreign keys

From: Roland Roberts <roberts(at)panix(dot)com>
To: pgsql-hackers(at)postgreSQL(dot)org, pgsql-sql(at)postgreSQL(dot)org
Subject: Help with pl/pgsql, triggers, and foreign keys
Date: 2000-01-29 04:22:54
Message-ID: m27lgt1p7l.fsf_-_@tycho.rlent.pnet
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

-----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(at)panix(dot)com 76-15 113th Street, Apt 3B
rbroberts(at)acm(dot)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-----

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message The Hermit Hacker 2000-01-29 05:02:21 Re: [HACKERS] Copyright
Previous Message Tatsuo Ishii 2000-01-29 03:53:05 Re: [HACKERS] Copyright

Browse pgsql-sql by date

  From Date Subject
Next Message Don Baccus 2000-01-29 05:56:06 Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys
Previous Message Nicolas Huillard 2000-01-28 22:31:34 RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4