Re: Multiple-statement Rules Incompatible With Constraints

Curt Sampson <cjs(at)cynic(dot)net> writes:
> CREATE VIEW offer AS
> SELECT * FROM offer_immutable NATURAL JOIN offer_mutable;

> In a transaction, when I try to commit, this does not work:

> CREATE OR REPLACE RULE offer_delete AS
> ON DELETE TO offer DO INSTEAD (
> DELETE FROM offer_mutable WHERE offer_id = OLD.offer_id;
> DELETE FROM offer_immutable WHERE offer_id = OLD.offer_id;
> );

Same old same old: as soon as you've deleted from offer_mutable, there
is no row in the view with the given offer_id; and since OLD is a macro
for the view, the second delete finds nothing to do.

You might be able to fix this by making the view an outer join, and
deleting from the nullable side first. The whole data structure seems a
tad weird though ...

regards, tom lane