3.20. Rules

In the object hierarchy under every table are Rules. You can add rules by selecting CREATE RULE from the Rules context menu. Once a rule has been created, it can be commented on, dropped, and replaced. This section describes how to create, comment on, and drop a rule with Administrator.

PostgreSQL allows you to define rules on tables or views (views are essentially tables in most respects). A rule allows the administrator to configure the database to execute certain events when a SELECT, UPDATE, INSERT, or DELETE takes place within the table.

In this example, you will create a rule called give_customer_discount on the table invoices.

1. A rule is an object under a table or view, so to create a rule, first expand the web_store database (click the [O-] beside the database name), then web_store_schema, then the invoices table.

2. Right-click on Rules and select CREATE RULE.

3. The first field in the dialog is the name of the rule. Type: give_customer_discount

4. In the On Event section, specify the event on which the rule should be executed. For this example, the event is INSERT.

5. In the Condition field, specify the condition that should be satisfied for the rule to be invoked. For this example, set the condition to: customer_id=10

6. In the Action field, specify the action that is to be performed when the event and condition described above occur. For this example, set the action to:

   UPDATE web_store_schema.invoices SET total_cost=total_cost*0.9::numeric(7,2) WHERE invoice_number=new.invoice_number;

7. Last is the Instead check box. If this is checked, the action in the Action field discussed above is performed instead of the requested action. For this example, leave Instead unchecked.

   The rule that you have specified says, "When adding an invoice to the database, if the invoice is for the customer with customer_id=10, set the total_cost to 9/10ths of the regular cost (that is, give this customer a 10% discount)."

   

   Figure 3-69. The CREATE RULE Dialog

8. Click OK to create the rule.

Note that the rule give_customer_discount has been added under the Rules node in the Tree View.

To learn more about rules, refer to the PostgreSQL 7.3 Reference Manual.

Administrator also enables you to perform the following operations on existing rules: