Re: Need help revoking access WHERE state = 'deleted'

Quoth lists-pgsql(at)useunix(dot)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 into entities_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