15.6. Lesson: Rules

Rules allow the "query tree" of an incoming query to be rewritten. One common usage is to implement views, including updatable view. - Wikipedia

The goal for this lesson: To learn how to create new rules for the database.

15.6.1. Creating a logging rule

Say you want to log every change of phone_no in your people table in to a people_log table. So you set up a new table:

createtablepeople_log(nametext,timetimestampdefaultNOW());

In the next step, create a rule that logs every change of a phone_no in the people table into the people_log table:

createrulepeople_logasonupdatetopeople
whereNEW.phone_no<>OLD.phone_no
doinsertintopeople_logvalues(OLD.name);

To test that the rule works, let’s modify a phone number:

updatepeoplesetphone_no='082 555 1234'whereid=2;

Check that the people table was updated correctly:

select*frompeoplewhereid=2;
id|name|house_no|street_id|phone_no
----+------------+----------+-----------+--------------
2|JoeBloggs|3|2|0825551234
(1row)

Now, thanks to the rule we created, the people_log table will look like this:

select*frompeople_log;
name|time
------------+----------------------------
JoeBloggs|2014-01-1114:15:11.953141
(1row)

Note

The value of the time field will depend on the current date and time.

15.6.2. In Conclusion

Rules allow you to automatically add or change data in your database to reflect changes in other parts of the database.

15.6.3. What’s Next?

The next module will introduce you to Spatial Database using PostGIS, which takes these database concepts and applies them to GIS data.