Unanswered: Postgres Rule for cascading delete on single table
I've having trouble setting up a rule to delete from a table when a delete is performed on that same table. I do this all the time fine with two tables, but I can't figure out how to do it with one. When dealing with two tables I do this:
CREATE RULE table1_delete AS ON DELETE TO table1 DO DELETE FROM table2 WHERE table2.table1_oid = OLD.oid;
So when an entry in table1 is deleted, and entries referencing it from table2 are also deleted.
Does anyone know how to do this when you're working on just one table? For example if we use this table:
> CREATE TABLE test(
> name VARCHAR(20),
> parent_test_oid OID
> INSERT INTO test VALUES ('parent', null);
INSERT 22567908 1
> INSERT INTO test VALUES ('child', 22567908);
INSERT 22567909 1
>SELECT oid, * FROM test;
oid | name | parent_test_oid
22567908 | parent |
22567909 | child | 22567908
As you can see I have associated the 'child' record with the parent through the value in parent_test_oid. So I try to set up a rule to delete the child if the parent is deleted:
CREATE RULE test_delete AS ON DELETE TO test DO DELETE FROM test WHERE test.parent_test_oid = OLD.oid;
And the rule is created without a problem. But when I delete the 'parent' record, I get this message:
ERROR: query rewritten 100 times, may contain cycles
Which suggests I've caused an infinite loop.
Note that I need to be able to have null values in the parent_test_oid column, so any solution involving rules or constraints would have to take that into account.
If anybody knows how to do this, any help would be very much appreciated.