Quoth lists-pgsql@useunix.net (Wayne Cuddy):
> On Thu, Feb 28, 2013 at 06:02:05PM +0000, Ben Morrow wrote:
> >
> > (If you wanted to you could instead rename the table, and use rules on
> > the view to transform DELETE to UPDATE SET state = 'deleted' and copy
> > across INSERT and UPDATE...)
>
> Sorry to barge in but I'm just curious... I understand this part
> "transform DELETE to UPDATE SET state = 'deleted'". Can you explain a
> little further what you mean by "copy across INSERT and UPDATE..."?
I should first say that AIUI the general recommendation is to avoid
rules (except for views), since they are often difficult to get right.
Certainly I've never tried to use rules in a production system.
That said, what I mean was something along the lines of renaming the
table to (say) entities_table, creating an entities view which filters
state = 'deleted', and then
create rule entities_delete as on delete to entities do instead update entities_table set state = 'deleted'
where key = OLD.key;
create rule entities_insert as on insert to entities where NEW.state != 'deleted' do instead insert
intoentities_table select NEW.*;
create rule entities_update as on update to entities where NEW.state != 'deleted' do instead update
entities_table set key = NEW.key, state = NEW.state, field1 = NEW.field1, field2 =
NEW.field2 where key = OLD.key;
(This assumes that "key" is the PK for entities, and that the state
field is visible in the entities view with values other than 'deleted'.
I don't entirely like the duplication of the view condition in the WHERE
clauses, but I'm not sure it's possible to get rid of it.)
This is taken straight out of the 'Rules on INSERT, UPDATE and DELETE'
section of the documentation; I haven't tested it, so it may not be
quite right, but it should be possible to make something along those
lines work.
Ben