Re: Infinite recursion detected... How do I prevent that?

# alban(at)magproductions(dot)nl / 2005-01-19 14:57:47 +0100:
> I have a rule similar to this:
>
> CREATE RULE rule_branch_delete AS
> ON DELETE TO tree
> DO DELETE
> FROM tree
> WHERE ancestor_id IS NOT NULL
> AND OLD.child_id = ancestor_id;

> If I try a delete on the tree table I get "Infinite recursion detected
> on rules on tree". I'm pretty sure it's not "infinite" in my case, how
> can I make it delete the records regardless this "infinity"?

cover the table with a view, as in:

CREATE TABLE _tree (
ancestor_id int,
child_id int
);

CREATE VIEW tree AS
SELECT * FROM _tree;

CREATE RULE rule_branch_delete AS
ON DELETE TO tree
DO INSTEAD (
DELETE FROM _tree ...; (the original DELETE redirected to _tree)
DELETE FROM _tree
WHERE ancestor_id IS NOT NULL
AND OLD.child_id = ancestor_id;
);

--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see http://www.eyrie.org./~eagle/faqs/questions.html