Received: from roberts.dialup.access.net (IDENT:root@roberts.dialup.access.net [166.84.193.218]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA34245; Fri, 28 Jan 2000 23:22:58 -0500 (EST) (envelope-from roberts@panix.com) Received: (from roland@localhost) by roberts.dialup.access.net (8.9.3/8.9.3) id XAA00848; Fri, 28 Jan 2000 23:22:54 -0500 Sender: roland@tycho.rlent.pnet To: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Help with pl/pgsql, triggers, and foreign keys Reply-To: roberts@panix.com From: Roland Roberts Date: 28 Jan 2000 23:22:54 -0500 In-Reply-To: "Ross J. Reedstrom"'s message of "Fri, 28 Jan 2000 16:21:15 -0600" Message-ID: Lines: 54 X-Mailer: Gnus v5.7/Emacs 20.4 -----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-----