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.
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.
Right-click on Rules and select CREATE RULE.
The first field in the dialog is the name of the rule. Type: give_customer_discount
In the On Event section, specify the event on which the rule should be executed. For this example, the event is INSERT.
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
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;
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)."
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:
PostgreSQL allows commenting on rules, which makes them easier to identify. To comment on a rule, right click on it, and select COMMENT. A dialog asks for the comment. If there is already a comment on the rule, this comment is displayed. Type the new comment in the edit box and click OK to save the new comment.
To drop a rule, right-click on it and select either DROP RESTRICT or DROP CASCADE. DROP RESTRICT will not drop the rule if there are any dependent objects. DROP CASCADE automatically drops the rule and all objects that depend on it.
If Preferences => Ask For Confirmations is enabled, you are asked to confirm that you want the rule dropped (and all the dependent objects in case of a DROP CASCADE); if it is disabled, the rule is dropped immediately (along with its dependent objects if DROP CASCADE was selected). This action cannot be undone.
Beginning in PostgreSQL 7.3, rules can be replaced. To replace a rule, right-click on the rule and select REPLACE RULE. The dialog that appears is similar to the CREATE RULE dialog and gives all the changeable options. For your convenience, the current values of the various options are displayed.