Quoth lists-pg...@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 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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to