PostgreSQL rule to delete record before insertion, if the record has changed

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