Re: PG rules! (RULES being the word ;->)

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