Papp Gyozo <s7461pap@hszk.bme.hu> writes:
>> 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
I don't think you can make that work --- once you've deleted the t_two
row for a particular c_i value, there's no longer any row in the
v_one_two join for that c_i, and so of course the second DELETE doesn't
find anything to delete. "old.c_i" isn't some magical constant, it's
just a shorthand for referring to the current contents of the rule's
event table, ie the v_one_two join. We can't change that behavior of
rules without breaking other cases that are at least as useful as this.
What you need is something that will hang onto the specific value you're
deleting for long enough to hit both tables. I haven't tried it but I
think you could do it with a function. Perhaps
... ON DELETE TO v_one_two DO INSTEAD SELECT delboth(old.c_i);
where delboth() is a SQL or PLPGSQL function that does the deletions
and then returns some dummy value. This should work because the
specific c_i value is held as a parameter of the function.
regards, tom lane