I’m not sure it’s possible to do this with a rule, but what I want to do is, when you insert a record:
- If it doesn’t exist, just insert it as usual.
- If it exists, but each field is equal to what you’re inserting, then do nothing.
- If it exists, but any field differs from what you’re inserting, then delete the old record and insert the new one.
The reason for this is that, in the last case, I want to use the cascade deletion productively. That is, if a record has changed, then everything that refers to it is now void and should be removed.
Let’s say my table is:
create table foo ( id integer primary key, foo integer, bar integer );
My first attempt was to just delete the old record regardless (before checking the difference):
create rule refresh as on insert to foo do instead ( delete from foo where id = new.id; insert into foo values (new.id, new.foo, new.bar));
This doesn’t work; it’s an infinite loop.
Is it possible to do what I’m after with rules, or would I need a full-blown trigger?
Go to Source