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 table 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 refer­
       enced 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  cre­
              ated.


DESCRIPTION

       The Postgres rule system allows one to define an alternate
       action to be performed on inserts, updates,  or  deletions

       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 speci­
       fied;  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 substituted 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 useless.

       The action part of the rule executes with the same command
       and transaction 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  contents  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 example, though each of the following two rule defini­
       tions  are  accepted  by Postgres, the select command will
       cause Postgres to report an error because the query cycled
       too many times:

       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.


Man(1) output converted with man2html