Thread
-
Help with pl/pgsql, triggers, and foreign keys
Roland Roberts <roberts@panix.com> — 2000-01-29T04:22:54Z
-----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-----