create_rule

CREATE RULE()			 SQL Commands			CREATE RULE()



NAME
       CREATE RULE - define a new rewrite rule


SYNOPSIS
       CREATE [ OR REPLACE ] RULE name AS ON event
	   TO table [ WHERE condition ]
	   DO [ INSTEAD ] { NOTHING | command | ( command ; command ... ) }


DESCRIPTION
       CREATE  RULE defines a new rule applying to a specified table or view.
       CREATE OR REPLACE RULE will either create a new rule,  or  replace  an
       existing rule of the same name for the same table.

       The PostgreSQL rule system allows one to define an alternate action to
       be performed on insertions, updates, or deletions in database  tables.
       Roughly	speaking,  a  rule  causes additional commands to be executed
       when a given command on a given table is	 executed.  Alternatively,  a
       rule can replace a given command by another, or cause a command not to
       be executed at all.  Rules are used to implement table views as	well.
       It is important to realize that a rule is really a command transforma-
       tion mechanism, or command macro. The  transformation  happens  before
       the  execution  of the commands starts. If you actually want an opera-
       tion that fires independently for each physical row, you probably want
       to  use a trigger, not a rule. More information about the rules system
       is in [XRef to RULES].

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

       You can create the illusion  of	an  updatable  view  by	 defining  ON
       INSERT,	ON UPDATE, and ON DELETE rules (or any subset of those that’s
       sufficient for your purposes) to replace update actions	on  the	 view
       with appropriate updates on other tables.

       There is a catch if you try to use conditional rules for view updates:
       there must be an unconditional INSTEAD rule for each action  you	 wish
       to  allow  on the view. If the rule is conditional, or is not INSTEAD,
       then the system will still  reject  attempts  to	 perform  the  update
       action, because it thinks it might end up trying to perform the action
       on the dummy table of the view in some cases. If you  want  to  handle
       all the useful cases in conditional rules, you can; just add an uncon-
       ditional DO INSTEAD NOTHING rule to ensure that the system understands
       it  will	 never be called on to update the dummy table.	Then make the
       conditional rules not INSTEAD; in the cases where  they	are  applied,
       they add to the default INSTEAD NOTHING action.

PARAMETERS
       name   The  name	 of  a rule to create. This must be distinct from the
	      name of any other rule for the same table.  Multiple  rules  on
	      the  same table and same event type are applied in alphabetical
	      name order.

       event  The even is one of SELECT, INSERT, UPDATE, or DELETE.

       table  The name (optionally schema-qualified) of the table or view the
	      rule applies to.

       condition
	      Any SQL conditional expression (returning boolean).  The condi-
	      tion expression may not refer to any tables except NEW and OLD,
	      and may not contain aggregate functions.

       command
	      The  command  or	commands  that make up the rule action. Valid
	      commands are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

       Within condition and command, the special table names NEW and OLD  may
       be  used	 to refer to values in the referenced table.  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 UPDATE and ON DELETE rules to refer to
       the existing row being updated or deleted.


NOTES
       You must have the privilege RULE on a table to be allowed to define  a
       rule on it.

       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
       PostgreSQL,  the	 SELECT	 command  would cause PostgreSQL to report an
       error because the query cycled too many times:

       CREATE RULE "_RETURN" AS
	   ON SELECT TO t1
	   DO INSTEAD
	    SELECT * FROM t2;

       CREATE RULE "_RETURN" AS
	   ON SELECT TO t2
	   DO INSTEAD
	    SELECT * FROM t1;

       SELECT * FROM t1;


       Presently, if a rule action contains a NOTIFY command, the NOTIFY com-
       mand  will  be  executed	 unconditionally, that is, the NOTIFY will be
       issued even if there are not any rows that the rule should  apply  to.
       For example, in

       CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable;

       UPDATE mytable SET name = ’foo’ WHERE id = 42;

       one  NOTIFY event will be sent during the UPDATE, whether or not there
       are any rows with id = 42. This is an implementation restriction	 that
       may be fixed in future releases.

COMPATIBILITY
       CREATE RULE is a PostgreSQL language extension, as is the entire rules
       system.



SQL - Language Statements	  2008-01-03			CREATE RULE()