Creating Custom Rules

I l @ ve RuBoard

Rules are very similar to triggers in concept, with some crucial differences. Triggers usually refer exclusively to the table being acted on, whereas rules act on external tables. Additionally, triggers are fired in addition to the action being carried out. For instance, an INSERT trigger will fire the event either before or after the insert is performed.

Rules, on the other hand, can also be created with the optional keyword INSTEAD . In this case, the rule action is carried out in lieu of the specified action.

A typical use of rules is to perform actions on external tables when a table- related event occurs on the specified table. A simple use of a rule set would be to implement the capability to log an audit trail of changes made to important tables. For instance, suppose the management wants to see a weekly report of every expenditure over $1000. You could implement this as follows :

 CREATE RULE log_payables AS        ON INSERT TO accounts_payable        WHERE new.amount > 1000 DO        INSERT INTO audit VALUES(new.vendor_id, new.amount, new.user); 

You could test this rule as follows:

 >SELECT * FROM accounts_payable;  vendor  amount  user  -------------------- 0 Results Found  >SELECT * FROM audit;  vendor  amount  user  -------------------- 0 Results Found  >INSERT INTO accounts_payable VALUES('Acme, Inc', 2500, 'Sean');  INSERT 231431 1  INSERT 231432 1  SELECT * FROM audit;  vendor     amount    user  ------------------------- Acme, Inc  2500.00   Sean 

Rules can also be used in conjunction with functions to create actions that are more complicated. For instance, suppose there are two tables in the database, payroll and paytotals . The payroll table holds an individual record for every payroll check issued. The paytotals table, however, has the latest year-to-date payroll totals for each employee.

In this case, it is assumed that it is important for the system to automatically keep the paytotals table up-to-date.A rule/function combination could be created to accomplish this, as follows:

 CREATE FUNCTION getpaytotal(int) RETURN real AS        'DECLARE              empid ALIAS FOR ;        BEGIN              SELECT sum(amount) AS paysum FROM payroll                    WHERE employee_id=empid;              RETURN paysum;        END;  ' LANGUAGE 'plpgsql'; 

Now that the function is created, it can be incorporated into a rule set:

 CREATE RULE compute_paytotal AS        ON INSERT TO payroll DO        UPDATE paytotals SET amount=getpaytotal(new.employee_id)              WHERE paytotals.employee_id=new.employee_id; 

The resultant rule can be tested as follows:

 >SELECT * FROM payroll;  empid  name   amount    checknum  -------------------------------- 123    Sean   100.00    5411  123    Sean   100.00    5412  >SELECT * FROM paytotals;  empid  name   amount  -------------------- 123    Sean   200.00  >INSERT INTO payroll VALUES (123, 'Sean', 200, 5413);  INSERT 243411 1  UPDATE 1  >SELECT * FROM paytotals;  empid  name   amount  -------------------- 123    Sean   400.00 

As previously mentioned, the CREATE RULE command also allows for the inclusion of the INSTEAD keyword. When this is specified, an alternative action will be performed.

Following from the previous example, let's assume management decided that any entry into the accounts_payable table that was over $1000 should be deferred into an alternate table until it was approved. For instance:

 CREATE RULE defer_ap AS        ON INSERT TO accounts_payable        WHERE new.amount > 1000 DO INSTEAD        INSERT INTO ap_hold VALUES (new.vendor, new.amount, new.user); 

This would result in any insert actions made into the accounts_payable table being redirected into the ap_hold table, pending management approval.

Unlike triggers, rules can also be defined to occur on SELECT statements. This can have some interesting implications. For instance, consider the following example:

 >SELECT * FROM accounts_payable;  vendor      amount   user  ------------------------- Widgets     500.00   Barry  Acme, Inc   2500.00  Sean  >CREATE TABLE my_ap INHERITS (accounts_payable);  >SELECT * FROM my_ap;  vendor      amount   user  ------------------------- Widgets     500.00   Barry  Acme, Inc   2500.00  Sean  >CREATE RULE my_select AS  ON SELECT TO my_ap DO INSTEAD  SELECT * FROM my_ap WHERE amount>1000;  >SELECT * FROM my_ap;  vendor      amount   user  ------------------------- Acme, Inc   2500.00  Sean 

In the preceding example, we've created a table that INHERITS all the attributes from the base table. Then a rule is defined on the new table that rewrites any SELECT statements and enforces a criterion match. The resultant action behaves suspiciously like a standard VIEW . This is not by accident because PostgreSQL actually uses rule definitions as the way that the CREATE VIEW command is implemented.

Notes and Considerations

Obviously, such uses for rule sets can be very beneficial in many cases. However, care must be taken when designing rule sets because there exists the potential for misuse. For instance, consider the following two related rules:

 CREATE RULE insert_1 AS        ON INSERT TO apple DO        INSERT INTO orange VALUES(new.weight);  CREATE RULE insert_2 AS        ON INSERT TO orange DO        INSERT INTO apple VALUES(new.weight); 

This example shows a dangerous potential of rule use. In this case, if an INSERT is made to either table, an infinite loop of cascading insert actions will begin. In actuality, PostgreSQL is too intelligent to allow this to happen, and the action would automatically fail once too many recursive queries are executed.

In general, however, rules should only point to tables that do not have any associated rules already set. That is, rule sets should point away from other rule sets. In large databases, which might have hundreds of tables, it can be extremely complicated to manage and predict results if numerous rules are actively engaged.

Additionally, rules only have access to specific system classes, namely to the OID attribute. This means that rule definitions cannot act directly on any system attributes. Therefore, functions such as func(table) will fail because table is considered a system class.

The code body for a particular rule can be accessed by viewing the pg_rules catalog.

I l @ ve RuBoard