Received: from mail.austin.rr.com (sm1.texas.rr.com [24.93.35.54]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA11853; Sat, 29 Jan 2000 03:36:00 -0500 (EST) (envelope-from eloehr@austin.rr.com) Received: from austin.rr.com ([24.27.30.243]) by mail.austin.rr.com with Microsoft SMTPSVC(5.5.1877.197.19); Sat, 29 Jan 2000 02:36:34 -0600 Sender: ed Message-ID: <3892A6C1.264C62B7@austin.rr.com> Date: Sat, 29 Jan 2000 02:37:21 -0600 From: Ed Loehr X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686) X-Accept-Language: en MIME-Version: 1.0 To: roberts@panix.com CC: pgsql-hackers@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [HACKERS] Help with pl/pgsql, triggers, and foreign keys References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Roland Roberts wrote: > > 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 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(); At least in 6.5.2, you can definitely implement referential integrity (RI) via pl/pgsql. As someone noted earlier, RI is to be released in 7.0, but I suspect it will take a subsequent release or two to stabilize before it's fit for consumption by the more conservative reliability-focused users among us... As for your failing SELECT query, the following tweak to your function makes it work as expected: CREATE FUNCTION check_task_pid () RETURNS OPAQUE AS ' DECLARE tmp RECORD; BEGIN IF NEW.task_pid IS NOT NULL THEN SELECT INTO tmp task_id FROM task WHERE task_id = NEW.task_pid; IF NOT FOUND THEN RAISE EXCEPTION ''FOREIGN KEY constraint task_pid not found''; END IF; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; Cheers, Ed Loehr