Introduction to PostgreSQL Rules - Making entries which can't be altered

by Justin Clift

Introduction

Have you tried out PostgreSQLs' "RULES" yet? If you haven't you're probably going to like this quick and practical introduction which you could find yourself using straight away!

PostgreSQL Rules' are used to intercept and change a query before it's executed. For example, lets say you have a table people can add stuff to, but you need to put 3 entries in the table which can never be changed or removed. This is how you do it :

Lets create an example table:

foo=> CREATE TABLE gift_certificates (idnum serial, 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)

For this example you'll be creating two 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

The "nothing" clause is a legitimate PostgreSQL Rule clause, effectively removing the actions where the SQL query would have updated any of the first 3 entries in this gift_certificates table. Still, all the SQL queries which are run on this table will work perfectly (except those attempting to update or delete any of these first 3 entries).

Out of interest, the WHERE clause can be any standard SQL WHERE clause. This example also has the addition of the "old" keyword to the idnum field. The "old.idnum" field kind of means "the version of idnum which existed before this attempted query began". Read the manual for more info. :)

Now, lets test things :

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 how that last update worked because it wasn't protected by the PostgreSQL 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 you find this useful!

For more information about PostgreSQL's Rules, refer to the PostgreSQL Programmer's Guide, "The Postgres Rule System".