.. only:: html


|LS| 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.

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:

.. code-block:: sql

  create table people_log (name text, time timestamp default NOW());

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

.. code-block:: sql

  create rule people_log as on update to people
    where NEW.phone_no <> OLD.phone_no
    do insert into people_log values (OLD.name);

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

.. code-block:: sql

  update people set phone_no = '082 555 1234' where id = 2;

Check that the :kbd:`people` table was updated correctly:

.. code-block:: sql

    select * from people where id=2;

     id |    name    | house_no | street_id |   phone_no
    ----+------------+----------+-----------+--------------
      2 | Joe Bloggs |        3 |         2 | 082 555 1234
    (1 row)

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

.. code-block:: sql

    select * from people_log;

        name    |            time
    ------------+----------------------------
     Joe Bloggs | 2014-01-11 14:15:11.953141
    (1 row)

.. note::  The value of the :kbd:`time` field will depend on the current date
   and time.

|IC|
-------------------------------------------------------------------------------

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

|WN|
-------------------------------------------------------------------------------

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


.. Substitutions definitions - AVOID EDITING PAST THIS LINE
   This will be automatically updated by the find_set_subst.py script.
   If you need to create a new substitution manually,
   please add it also to the substitutions.txt file in the
   source folder.

.. |IC| replace:: In Conclusion
.. |LS| replace:: Lesson:
.. |WN| replace:: What's Next?