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