We sometimes need to perform a different/alternative actions during database queries. We need it for data protection or data abstraction. The PostgreSQL rule system allows to define an alternative action on insert, update or delete. A rule generates an extra query. As a result, rule execution impacts the performance of the system.

Creating Rules

A rule can be created using the following syntax:

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table_name [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

CREATE OR REPLACE RULE will either create a new rule, or replace an existing rule of the same name for the same table.

Parameters:

1. name: The name of the rule to create or replace
2. event: The event is one out of - SELECT, INSERT, UPDATE or DELETE
3. table_name: The name of the table (or view) where this rule applies
4. condition: An SQL expression to further specify when this rule needs to run
5. INSTEAD: Instead defines what is the alternative course of action
6. ALSO: It defines the commands to be executed in addition to the original commands. If neither INSTEAD nor ALSO keyword is mentioned the default is ALSO
7. command: The alternative action that needs to be performed

Within condition and command, we can use the special table names NEW and OLD to refer to the values in the referenced table (mentioned in table_name). The table name NEW is valid in on INSERT and on UPDATE rules to refer to the new row being inserted or updated. The table name OLD is valid in on UPDATE and on DELETE rules to refer to the existing row being updated or deleted.

Creating our first rule

Suppose we have a requirement that we should never update any details for the employee with first name John and last name Smith in the EMPLOYEE table. To achieve this requirement using rule, we can create a rule as:

CREATE OR REPLACE RULE PROTECT_JOHN_SMITH 
AS ON UPDATE TO EMPLOYEE 
WHERE old.FIRST_NAME = 'JOHN' 
AND old.last_name = 'SMITH'
DO INSTEAD NOTHING;

Now if we run an update query as:

UPDATE EMPLOYEE SET FIRST_NAME = 'ALIA' WHERE FIRST_NAME = 'JOHN';

No rows will be updated.

Practical Scenarios

Here are a few common scenarios where rules are useful.

Use case: 1

Suppose we have a requirement of not letting anyone update any data in the employee table. To achieve this using a rule, we can write:

CREATE RULE UPDATE_EMPLOYEE AS ON UPDATE TO EMPLOYEE 
    DO INSTEAD
    NOTHING;

Once the rule is created, try running the query:

UPDATE EMPLOYEE SET EMPLOYEE_ID = 11111125 WHERE FIRST_NAME = 'JOHN';

No rows are updated.

Use case: 2

Suppose there is a requirement to track any updates done to the EMPLOYEE table in another log table called EMPLOYEE_LOG. To achieve this using rule, we first need to have the EMPLOYEE_LOG table as follows:

CREATE TABLE EMPLOYEE_LOG (
    EMPLOYEE_NAME    TEXT, 
    EMPLOYEE_ID   INTEGER, 
    EMPLOYEE_USER    TEXT, 
    CREATE_DATETIME   TIMESTAMP 
);

Next we can create a rule as follows:

CREATE RULE LOG_EMPLOYEE AS ON UPDATE TO EMPLOYEE
    WHERE NEW.EMPLOYEE_ID <> OLD.EMPLOYEE_ID
    DO INSERT INTO EMPLOYEE_LOG VALUES (
                                    NEW.FIRST_NAME,
                                    NEW.EMPLOYEE_ID,
                                    CURRENT_USER,
                                    CURRENT_TIMESTAMP
                                );

Once the rule is created, we can now run an update query into the database as:

UPDATE EMPLOYEE SET EMPLOYEE_ID = 6 WHERE FIRST_NAME = 'JOHN';

This will update the qualifying row in EMPLOYEE table and also make an entry into the EMPLOYEE_LOG table as:

Other Notes

The following points need to be taken care during a rule creation (or updating) in database:

1. We must be the owner of a table to create or change rules for it.
2. It is very important to take care to avoid circular rules.

An example of circular rule is like:

CREATE RULE "RETURN_STAFF" AS
    ON SELECT TO STAFF
    DO INSTEAD
        SELECT * FROM DEPARTMENT;
CREATE RULE "RETURN_DEPARTMENT" AS
    ON SELECT TO DEPARTMENT
    DO INSTEAD
        SELECT * FROM STAFF;

Now let us invoke the query:

SELECT * FROM STAFF;

The 2 rules we created above is contradictory and has created a circular dependency. We mentioned that a SELECT command on STAFF table should INSTEAD return DEPARTMENT rows. We also mentioned that SELECT command on DEPARTMENT table should INSTEAD return STAFF rows. So we should avoid these situation in reality.

Conclusion

There are many more things that we can do using PostgreSQL rules. However, it is advisable not to use rules and rather use triggers instead. Rules come really handy in situations like where multiple rows get qualified for an update query . In such case, the rule generates only 1 additional query for all the qualifying rows as a whole. This benefits the performance of the database. We will also touch base more complex scenarios of rule creation with different practical use cases in an upcoming article.