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
Author: Xophmeister