From: Ben Morrow <ben@morrow.me.uk>
To: lists-pgsql@useunix.net
To: pgsql-sql@postgresql.org
Subject: Re: Need help revoking access WHERE state = 'deleted'
Date: Sat, 2 Mar 2013 23:45:38 +0000
Message-ID: <20130302234535.GA44562@anubis.morrow.me.uk> (raw)
In-Reply-To: <20130302182005.GG18317@slacker.ja10629.home>
References: <kgo14h$vm3$1@ger.gmane.org>
<20130228180201.GA10412@anubis.morrow.me.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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 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 instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ben@morrow.me.uk, lists-pgsql@useunix.net
Subject: Re: Need help revoking access WHERE state = 'deleted'
In-Reply-To: <20130302234535.GA44562@anubis.morrow.me.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox