From: | Justin Clift <justin(at)postgresql(dot)org> |
---|---|
To: | "Dr(dot) Evil" <drevil(at)sidereal(dot)kz> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PG rules! (RULES being the word ;->) |
Date: | 2001-07-18 02:57:47 |
Message-ID: | 3B54FB2B.7BAFEAD8@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | will trillich | 2001-07-18 03:21:15 | Re: How do system tables relate to other tables in postgresql |
Previous Message | Keith F Irwin | 2001-07-18 02:44:49 | HELP! BUG? pg_dump mucks up grant/revoke |