<!--
doc/src/sgml/ref/create_rule.sgml
PostgreSQL documentation
-->

<refentry id="sql-createrule">
 <indexterm zone="sql-createrule">
  <primary>CREATE RULE</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE RULE</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE RULE</refname>
  <refpurpose>define a new rewrite rule</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
    TO <replaceable class="parameter">table_name</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
    DO [ ALSO | INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }

<phrase>where <replaceable class="parameter">event</replaceable> can be one of:</phrase>

    SELECT | INSERT | UPDATE | DELETE
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

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

  <para>
   The <productname>PostgreSQL</productname> rule system allows one to
   define an alternative 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, an <literal>INSTEAD</literal>
   rule can replace a given command by another, or cause a command
   not to be executed at all.  Rules are used to implement SQL
   views as well.  It is important to realize that a rule is really
   a command transformation mechanism, or command macro.  The
   transformation happens before the execution of the command starts.
   If you actually want an operation 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 linkend="rules"/>.
  </para>

  <para>
   Presently, <literal>ON SELECT</literal> rules can only be attached
   to views.  Such a rule must be named <literal>"_RETURN"</literal>,
   must be an unconditional <literal>INSTEAD</literal> rule, and must have
   an action that consists of a single <command>SELECT</command> command.
   This command defines the visible contents of the view.  (The view
   itself is basically a dummy table with no storage.)  It's best to
   regard such a rule as an implementation detail.  While a view can be
   redefined via <literal>CREATE OR REPLACE RULE "_RETURN" AS
   ...</literal>, it's better style to use <literal>CREATE OR REPLACE
   VIEW</literal>.
  </para>

  <para>
   You can create the illusion of an updatable view by defining
   <literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and
   <literal>ON DELETE</literal> 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.  If you want to support
   <command>INSERT RETURNING</command> and so on, then be sure to put a suitable
   <literal>RETURNING</literal> clause into each of these rules.
  </para>

  <para>
   There is a catch if you try to use conditional rules for complex view
   updates: there <emphasis>must</emphasis> be an unconditional
   <literal>INSTEAD</literal> rule for each action you wish to allow
   on the view.  If the rule is conditional, or is not
   <literal>INSTEAD</literal>, 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