Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

CREATE RULE

Name

CREATE RULE -- Defines a new rule on a table.

Synopsis

CREATE RULE 
name
 AS ON 
event
 TO 
object
 [ WHERE 
condition
 ] DO [ INSTEAD ]  
action


action
 ::= NOTHING | query | ( query [; ...] ) | [ query [; ...] ]

Parameters

name

The name of the new rule you are creating.

event

The event that triggers the rule. This parameter should be one of: SELECT, UPDATE, DELETE, or INSERT.

object

The name of a table, or the fully qualified name of a table column (e.g., table_name.column_name ).

condition

A SQL condition evaluating to a value of type boolean, which specifies when this rule should be used. This statement should not refer to a table; the only exception to this is that the condition may refer to the special new and old relations, which represent the existing rows, and any new row data provided, respectively.

INSTEAD

The INSTEAD keyword; when used, the action is executed instead of the specified event . Otherwise, the action executes before the event does.

action

The query (or queries) that define the action to perform when the rule is triggered, and the condition is met. The query (or queries) can be any valid SELECT, INSERT, UPDATE, DELETE, or NOTIFY statements. Supply multiple queries by surrounding them in parentheses.

You may alternatively use the NOTHING keyword instead of a query. NOTHING will perform no action, and is only useful if you also specify the INSTEAD keyword.

Within the condition and action values, you are able to use the special new and old relations to access column values from both the referenced object , and from the data triggering the rule.

The new relation is available in an INSERT or UPDATE rule, containing the column values being inserted or updated, while the old relation is available in a SELECT, UPDATE, or DELETE rule, containing the row data being selected, updated, or deleted.

Results

CREATE

The message returned when a rule is successfully created.

Description

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 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 specified condition is met, 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 the action will be performed before the query is processed (the event itself). 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.

Note: When using ON SELECT rules, you must pass the INSTEAD parameter. Essentially, this means that ON SELECT must always implement table views.

Examples

The following example shows the definition of a rule named sync_stock_with_editions that updates the stock table's isbn column automatically when the editions table is modified:

booktown=# 
CREATE RULE sync_stock_with_editions AS

booktown-# 
            ON UPDATE TO editions

booktown-# 
            DO UPDATE stock SET isbn = new.isbn

booktown-# 
                            WHERE isbn = old.isbn;

CREATE
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire