Hello,
I came up against the same (or similar) difficulty some month ago.
Then Tom Lane suggested me to write a simple sql or plpgsql function which handles the
multiple delete action.
(BTW, maybe you don't want to use the on delete cascade referential integrity in some
cases.)
Sorry, but I can't find anywhere the answer sent me.
But here's the source code of the function I wrote:
create function f_privateauto_del(ID) returns BOOLEAN as
'DECLARE ID INTEGER;
BEGIN
SELECT INTO ID c_id FROM t_one WHERE t_one.c_id = $1;
IF NOT FOUND THEN RAISE EXCEPTION ''Illegal parameter''; END IF; -- this isn't
necessary
DELETE FROM t_one WHERE oid = $1;
DELETE FROM t_two WHERE c_id = ID;
RETURN true::BOOLEAN;
END;'
language 'plpgsql';
You can figure out from my original letter (below) how much your problems is similar
to the one I had.
Papp Gyozo
- [EMAIL PROTECTED]
>From [EMAIL PROTECTED] Mon Sep 18 12:04:39 2000 +0200
Date: Mon, 18 Sep 2000 12:04:33 +0200 (MET DST)
From: Papp Gyozo <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: rule with multiple DELETE action part
Hello,
now, my only question is how I can make a rule on a view with multiple delete action
part which
works well. I need a mechanism which deletes two rows from two tables
which are in a one-to-one join.
The example listed below is a quite different from the real tables I use.
The table in the same role as "t_two" references the other table ("t_one").
If rules with multiple action don't work correctly this would mean that
my only last chance is using the REFERENCES constraint with ON DELETE CASCADE option?
I hope not, because it is not for the same, I want to deny to delete rows
if it has a pair in the other table.
By the way, multiple inserts seem to work.
try=# CREATE TABLE t_one (c_i integer PRIMARY KEY, c_t text);
CREATE
try=# CREATE TABLE t_two (c_i integer REFERENCES t_one, c_d date);
CREATE
try=# CREATE VIEW v_one_two AS SELECT t_one.oid as c_oid, t_one.c_i, c_d, c_t
try-# FROM t_one, t_two WHERE t_one.c_i = t_two.c_i;
CREATE 81186 1
try=# CREATE RULE r_one_two_del AS ON DELETE TO v_one_two
try-# DO INSTEAD (DELETE FROM t_two WHERE c_i = old.c_i; DELETE FROM t_one WHERE c_i =
old.c_i;);
CREATE 81187 1
try=# SELECT * FROM v_one_two;
c_oid | c_i | c_d | c_t
-------+-----+------------+-------
81157 | 1 | 2000-09-01 | hello
81158 | 2 | 1999-12-31 | world
81159 | 3 | 2000-08-12 | brave
(3 rows)
try=# DELETE FROM v_one_two WHERE c_i = 2;
DELETE 0
try=# SELECT * FROM t_one;
c_i | c_t
-----+-------
1 | hello
2 | world
3 | brave
4 | guy
(4 rows)
try=# SELECT * FROM t_two;
c_i | c_d
-----+------------
1 | 2000-09-01
3 | 2000-08-12
(2 rows)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
