Re: PG rules! (RULES being the word ;->) - Mailing list pgsql-general

Heya Dr. Evil,

Have you tried out RULES yet?  (CREATE RULE)

They're even niftier.  :-)

Let say you have a table people can add stuff to, but you need to put 3
entries in the table which can never be deleted, you use CREATE RULE.

i.e.

Lets create an example table :

foo=> CREATE TABLE gift_certificates (idnum serial unique not null,
person varchar(20), amount float4);
NOTICE:  CREATE TABLE will create implicit sequence
'gift_certificates_idnum_seq' for SERIAL column
'gift_certificates.idnum'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index
'gift_certificates_idnum_key' for table 'gift_certificates'
CREATE

Lets give it some data :

foo=> insert into gift_certificates (person, amount) values ('Justin',
200);
INSERT 51564057 1
foo=> insert into gift_certificates (person, amount) values ('Tom',
200);
INSERT 51564059 1
foo=> insert into gift_certificates (person, amount) values ('Richard',
200);
INSERT 51564062 1
foo=> insert into gift_certificates (person, amount) values ('Peter',
200);
INSERT 51564065 1
foo=> insert into gift_certificates (person, amount) values ('Bruce',
200);
INSERT 51564066 1
foo=> insert into gift_certificates (person, amount) values ('Marc',
200);
INSERT 51564067 1
foo=> insert into gift_certificates (person, amount) values ('Vince',
200);

foo=> select * from gift_certificates;
 idnum | person  | amount
-------+---------+--------
     1 | Justin  |    200
     2 | Tom     |    200
     3 | Richard |    200
     4 | Peter   |    200
     5 | Bruce   |    200
     6 | Marc    |    200
     7 | Vince   |    200
(7 rows)

Lets add two everyday useful example rules :

foo=> CREATE RULE prot_gc_upd AS ON UPDATE TO gift_certificates WHERE
old.idnum < 4 DO INSTEAD nothing;
CREATE
foo=> CREATE RULE prot_gc_del AS ON DELETE TO gift_certificates WHERE
old.idnum < 4 DO INSTEAD nothing;
CREATE

So here, all the normal SQL queries work except those which would
specifically update or delete any of the first 3 entries in this
gift_certificates table.

foo=> update gift_certificates set person = 'Justin2' where idnum = 1;
UPDATE 0
foo=> update gift_certificates set person = 'Justin2' where idnum = 2;
UPDATE 0
foo=> update gift_certificates set person = 'Justin2' where idnum = 3;
UPDATE 0
foo=> update gift_certificates set person = 'Justin2' where idnum = 4;
UPDATE 1

See, that last one worked because it wasn't protected by the rules?

foo=> select * from gift_certificates;
 idnum | person  | amount
-------+---------+--------
     1 | Justin  |    200
     2 | Tom     |    200
     3 | Richard |    200
     5 | Bruce   |    200
     6 | Marc    |    200
     7 | Vince   |    200
     4 | Justin2 |    200
(7 rows)

foo=>

And the delete rule from up above works as well :

foo=> delete from gift_certificates;
DELETE 4
foo=> select * from gift_certificates;
 idnum | person  | amount
-------+---------+--------
     1 | Justin  |    200
     2 | Tom     |    200
     3 | Richard |    200
(3 rows)

foo=>


Cool eh?

Hope that's useful!  (We should prolly put this in the PostgreSQL
tutorial somewhere....)

:-)

Regards and best wishes,

Justin Clift




"Dr. Evil" wrote:
>
> I just want to say, that PG is an awesome thing.  I'm finding new uses
> for constraints of various kinds to ensure data integrity in my DB.
> Constraints will really make the whole application more solid, because
> programming errors elsewhere still won't allow corrupt data to get
> into the DB.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster