CREATE RULE name AS ON event TO object [ WHERE condition ] DO [ INSTEAD ] actionThe possible options for action are: NOTHING | query | (query; query...) | [query;query...]
This parameter takes the name of the new rule you are creating.
This parameter should be one of: SELECT, UPDATE, DELETE , or INSERT.
The object parameter accepts one of two things: table or table.column. As of the most current implementation, only tableis supported.
This parameter accepts any boolean SQL statement which does not refer to a table; an exception is that the statement may refer to the new and old references to values in the table object.
Use this parameter to specify a query (or queries) that make up the action. The query (or queries) can be any valid SELECT, INSERT, UPDATE, DELETE , or NOTIFY statement. Supply multiple queries by surrounding them in parentheses; or, you may specify "NOTHING" instead of a query. This will perform no action (only useful if you also pass the INSTEAD clause).
|new and old|
When specifying the condition and action , you are allowed to use new and old to access values from the referenced table, object. You can use new in an ON INSERT or ON UPDATE rule, while old can be used in an ON SELECT, ON UPDATE< or ON DELETE rule. The special table names new and old may be used within the condition and action to refer to values in the referenced table (the object). The word new in an ON INSERT or ON UPDATE rule refers to the new row being inserted or updated. The word old in an ON SELECT, ON UPDATE, or ON DELETE rule refers to the existing row being selected, updated, or deleted.
Use CREATE RULE to create a rule. Rules allow you to define alternate actions to be taken upon table and class inserts, updates, and deletions. You can also use the PostgreSQL rule system to implement table views.
When a SELECT, INSERT , DELETE, or UPDATE is issued, the rules for that event are examined in an unspecified order. If a WHERE clause has been specified by the rule, it is checked; if the event passes the supplied condition, the rule's specified action is performed. If you specified INSTEAD when creating the rule, the action will be taken instead of the event; otherwise it will be performed before the query is processed. Be careful not to create what are known as circular rules; these are rules that reference other rules that in turn reference the original rule.
When using ON SELECT rules, you must pass the INSTEAD parameter. Essentially, this means that ON SELECT rules will be table views.
The following example demonstrates the definition of a rule that will update a stock amount when another product is changed.
CREATE RULE alt_select AS ON SELECT TO inventory DO INSTEAD SELECT * FROM new_inventory;