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