Rules

 Rules allow actions to take place when a table is accessed. In this way, they can modify the effects of SELECT, INSERT, UPDATE, and DELETE.

Figure  shows a rule that prevents INSERTs into a table.  

        test=> CREATE TABLE ruletest (col INTEGER);
        CREATE
        test=> CREATE RULE ruletest_insert AS     -- rule name
        test-> ON INSERT TO ruletest              -- INSERT rule
        test-> DO INSTEAD                         -- DO INSTEAD-type rule
        test->     NOTHING;                       -- ACTION is NOTHING
        CREATE 18932 1
        test=> INSERT INTO ruletest VALUES (1);
        test=> SELECT * FROM ruletest;
         col 
        -----
        (0 rows)  

The INSERT rule is named ruletest_insert and the action is NOTHING. NOTHING is a special rule keyword that does nothing.

Two types of rules exist. DO rules perform SQL commands in addition to the submitted query. DO INSTEAD  rules replace the user query with the rule action.

Figure  shows how rules can track table changes.  

        test=> CREATE TABLE service_request (
        test->                    customer_id INTEGER,
        test->                    description text,
        test->                    cre_user text DEFAULT CURRENT_USER,
        test->                    cre_timestamp timestamp DEFAULT CURRENT_TIMESTAMP );
        CREATE
        test=> CREATE TABLE service_request_log (
        test->                    customer_id INTEGER,
        test->                    description text,
        test->                    mod_type char(1),
        test->                    mod_user text DEFAULT CURRENT_USER,
        test->                    mod_timestamp timestamp DEFAULT CURRENT_TIMESTAMP);
        CREATE
        test=> CREATE RULE service_request_update AS     -- UPDATE rule
        test-> ON UPDATE TO service_request 
        test-> DO 
        test->     INSERT INTO service_request_log (customer_id, description, mod_type)
        test->     VALUES (old.customer_id, old.description, 'U');
        CREATE 19670 1
        test=> CREATE RULE service_request_delete AS     -- DELETE rule
        test-> ON DELETE TO service_request 
        test-> DO
        test->     INSERT INTO service_request_log (customer_id, description, mod_type)
        test->     VALUES (old.customer_id, old.description, 'D');
        CREATE 19671 1  

In the figure, service_request holds current service requests, and service_request_log records changes in the service_request table. The figure also creates two DO rules on service_request. The rule service_request_update causes an INSERT into service_request_log each time that service_request is updated. The special keyword old is used to insert the pre-UPDATE column values into service_request_log; the keyword new would refer to the new query values. The second rule, service_request_delete, tracks deletions to service_request by inserting into service_request_log. To distinguish updates from deletes in service_request_log, updates are inserted with a mod_type of 'U' and deletes with a mod_type of 'D'.

In figure , DEFAULT  was used for the user name and timestamp fields. A column's default value is used when an INSERT does not supply a value for the column. In this example, defaults allow auto-assignment of these values on INSERT to service_request, and on rule INSERTs to service_request_log.

Figure  demonstrates the use of these rules.

 

        test=> INSERT INTO service_request (customer_id, description)
        test-> VALUES (72321, 'Fix printing press');
        INSERT 18808 1
        test=> UPDATE service_request 
        test-> SET description = 'Fix large printing press'
        test-> WHERE customer_id = 72321;
        UPDATE 1
        test=> DELETE FROM service_request 
        test-> WHERE customer_id = 72321;
        DELETE 1
        test=> SELECT * 
        test-> FROM service_request_log
        test-> WHERE customer_id = 72321;
         customer_id |       description        | mod_type | mod_user |     mod_timestamp
        -------------+--------------------------+----------+----------+------------------------
               72321 | Fix printing press       | U        | williams | 2000-04-09 07:13:07-04
               72321 | Fix large printing press | D        | matheson | 2000-04-10 12:47:20-04
        (2 rows)  

A row is inserted, updated, and deleted from service_request. A SELECT on service_request_log shows the UPDATE rule recorded the pre-UPDATE values, a U in mod_type, and the user, date, and time of the UPDATE. The DELETE  rule follows a similar pattern.

 Although views ignore INSERT, UPDATE, and DELETE , rules can be used to properly handle them. Figure  shows the creation of a table and a view on the table.

 

        test=> CREATE TABLE realtable (col INTEGER);
        CREATE
        test=> CREATE VIEW view_realtable AS SELECT * FROM realtable;
        CREATE 407890 1
        test=> INSERT INTO realtable VALUES (1);
        INSERT 407891 1
        test=> INSERT INTO view_realtable VALUES (2);
        INSERT 407893 1
        test=> SELECT * FROM realtable;
         col 
        -----
           1
        (1 row) 
         
        test=> SELECT * FROM view_realtable;
         col 
        -----
           1
        (1 row)  

In the figure, INSERTs into a view are ignored, as are UPDATEs and DELETEs.

Figure  shows the creation of DO INSTEAD rules to properly handle INSERT, UPDATE, and DELETE .

 

        test=> CREATE RULE view_realtable_insert AS     -- INSERT rule
        test-> ON INSERT TO view_realtable 
        test-> DO INSTEAD 
        test->     INSERT INTO realtable 
        test->     VALUES (new.col);
        CREATE 407894 1
        test=>
        test=> CREATE RULE view_realtable_update AS     -- UPDATE rule
        test-> ON UPDATE TO view_realtable 
        test-> DO INSTEAD 
        test->     UPDATE realtable 
        test->     SET col = new.col 
        test->     WHERE col = old.col;
        CREATE 407901 1
        test=>
        test=> CREATE RULE view_realtable_delete AS     -- DELETE rule
        test-> ON DELETE TO view_realtable 
        test-> DO INSTEAD 
        test->     DELETE FROM realtable 
        test->     WHERE col = old.col;
        CREATE 407902 1  

This procedure involves changing INSERT, UPDATE, and DELETE  queries on the view to queries on realtable. Notice that the INSERT rule uses new to reference the new value to be inserted. In contrast, UPDATE and DELETE  use old to reference old values. Figure  shows how the view properly handles modifications.  

        test=> INSERT INTO view_realtable VALUES (3);
        INSERT 407895 1
        test=> SELECT * FROM view_realtable;
         col 
        -----
           1
           3
        (2 rows) 
         
        test=> UPDATE view_realtable 
        test-> SET col = 4;
        UPDATE 2
        test=> SELECT * FROM view_realtable;
         col 
        -----
           4
           4
        (2 rows) 
         
        test=> DELETE FROM view_realtable;
        DELETE 2
        test=> SELECT * FROM view_realtable; 
         col 
        -----
        (0 rows)  

It would be wise to add an index on col because the rules do lookups on that column.   

You can also create SELECT rules. In fact, views are implemented internally as SELECT rules. Rules can even be applied to only certain rows. To remove them, use DROP RULE command. See the CREATE_RULE and DROP_RULE manual pages for more information.  

Creating a rule whose action performs the same command on the same table causes an infinite loop. That is, POSTGRESQL will call the rule again and again from the rule action. For example, if an UPDATE rule on ruletest has a rule action that also performs an UPDATE on ruletest, it will cause an infinite loop. POSTGRESQL will detect the infinite loop and return an error.

Fortunately, POSTGRESQL also supports triggers.  Triggers allow actions to be performed when a table is modified. In this way, they can perform actions that cannot be implemented using rules. See Section  for information on the use of triggers.