create_rule man page on BSDOS

Man page or keyword search:  
man Server   6284 pages
apropos Keyword Search (all sections)
Output format
BSDOS logo
[printable version]

CREATE RULE()			 SQL Commands			 CREATE RULE()

NAME
       CREATE RULE - Defines a new rule

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

       where action can be:

       NOTHING
       |
       query
       |
       ( query ; query ... )
       |
       [ query ; query ... ]

   INPUTS
       name   The name of a rule to create.

       event  Event is one of SELECT, UPDATE, DELETE or INSERT.

       object Object is either table or table.column. (Currently, only the ta‐
	      ble form is actually implemented.)

       condition
	      Any SQL boolean-condition expression. The	 condition  expression
	      may not refer to any tables except new and old.

       query  The query or queries making up the action can be any SQL SELECT,
	      INSERT, UPDATE, DELETE, or NOTIFY statement.

       Within the condition and action, the special table names	 new  and  old
       may  be	used  to refer to values in the referenced table (the object).
       new is valid in ON INSERT and ON UPDATE rules to refer to the  new  row
       being  inserted	or updated.  old is valid in ON SELECT, ON UPDATE, and
       ON DELETE rules to refer to the existing row being  selected,  updated,
       or deleted.

   OUTPUTS
       CREATE Message returned if the rule is successfully created.

DESCRIPTION
       The Postgres rule system allows one to define an alternate action to be
       performed on inserts, updates, or deletions from database tables. Rules
       are used to implement table views as well.

       The  semantics  of  a  rule  is that at the time an individual instance
       (row) is accessed, inserted, updated,  or  deleted,  there  is  an  old
       instance	 (for  selects,	 updates  and deletes) and a new instance (for
       inserts and updates). All the rules for the given event	type  and  the
       given  target  object (table) are examined, in an unspecified order. If
       the condition specified in the WHERE  clause  (if  any)	is  true,  the
       action  part of the rule is executed. The action is done instead of the
       original query if INSTEAD is specified; otherwise it is done before the
       original	 query	is  performed.	 Within both the condition and action,
       values from fields in the old instance and/or the new instance are sub‐
       stituted for old.attribute-name and new.attribute-name.

       The  action  part  of  the  rule can consist of one or more queries. To
       write multiple queries, surround them with either parentheses or square
       brackets.  Such	queries	 will  be  performed  in  the  specified order
       (whereas there are no guarantees about the execution order of  multiple
       rules  for  an  object).	 The  action can also be NOTHING indicating no
       action. Thus, a DO INSTEAD NOTHING rule suppresses the  original	 query
       from  executing (when its condition is true); a DO NOTHING rule is use‐
       less.

       The action part of the rule executes with the same command and transac‐
       tion identifier as the user command that caused activation.

   NOTES
       Presently, ON SELECT rules must be unconditional INSTEAD rules and must
       have actions that consist of a single SELECT query. Thus, an ON	SELECT
       rule effectively turns the object table into a view, whose visible con‐
       tents are the rows returned by the  rule's  SELECT  query  rather  than
       whatever	 had  been stored in the table (if anything). It is considered
       better style to write a CREATE VIEW command than to create a table  and
       define an ON SELECT rule for it.

       You  must  have	rule definition access to a table in order to define a
       rule on it. Use GRANT and REVOKE to change permissions.

       It is very important to take care to avoid circular rules.   For	 exam‐
       ple,  though each of the following two rule definitions are accepted by
       Postgres, the select command will cause Postgres	 to  report  an	 error
       because the query cycled too many times:

   EXAMPLE OF A CIRCULAR REWRITE RULE COMBINATION:
       CREATE RULE bad_rule_combination_1 AS
	   ON SELECT TO emp
	   DO INSTEAD
	    SELECT * FROM toyemp;

       CREATE RULE bad_rule_combination_2 AS
	   ON SELECT TO toyemp
	   DO INSTEAD
	    SELECT * FROM emp;

       This  attempt  to select from EMP will cause Postgres to issue an error
       because the queries cycled too many times:

       SELECT * FROM emp;

COMPATIBILITY
   SQL92
       CREATE RULE statement is a Postgres language extension.	 There	is  no
       CREATE RULE statement in SQL92.

SQL - Language Statements	 29 March 2001			 CREATE RULE()
[top]
                             _         _         _ 
                            | |       | |       | |     
                            | |       | |       | |     
                         __ | | __ __ | | __ __ | | __  
                         \ \| |/ / \ \| |/ / \ \| |/ /  
                          \ \ / /   \ \ / /   \ \ / /   
                           \   /     \   /     \   /    
                            \_/       \_/       \_/ 
More information is available in HTML format for server BSDOS

List of man pages available for BSDOS

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net