doc/src/sgml/ref/create_rule.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATERULE">
   7  <refmeta>
   8   <refentrytitle id="sql-createrule-title">CREATE RULE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>CREATE RULE</refname>
  15   <refpurpose>define a new rewrite rule</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-createrule">
  19   <primary>CREATE RULE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
  25     TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
  26     DO [ ALSO | INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }
  27 </synopsis>
  28  </refsynopsisdiv>
  29
  30  <refsect1>
  31   <title>Description</title>
  32
  33   <para>
  34    <command>CREATE RULE</command> defines a new rule applying to a specified
  35    table or view.
  36    <command>CREATE OR REPLACE RULE</command> will either create a
  37    new rule, or replace an existing rule of the same name for the same
  38    table.
  39   </para>
  40
  41   <para>
  42    The <productname>PostgreSQL</productname> rule system allows one to
  43    define an alternative action to be performed on insertions, updates,
  44    or deletions in database tables.  Roughly speaking, a rule causes
  45    additional commands to be executed when a given command on a given
  46    table is executed.  Alternatively, an <literal>INSTEAD</literal>
  47    rule can replace a given command by another, or cause a command
  48    not to be executed at all.  Rules are used to implement table
  49    views as well.  It is important to realize that a rule is really
  50    a command transformation mechanism, or command macro.  The
  51    transformation happens before the execution of the commands starts.
  52    If you actually want an operation that fires independently for each
  53    physical row, you probably want to use a trigger, not a rule.
  54    More information about the rules system is in <xref linkend="rules">.
  55   </para>
  56
  57   <para>
  58    Presently, <literal>ON SELECT</literal> rules must be unconditional
  59    <literal>INSTEAD</literal> rules and must have actions that consist
  60    of a single <command>SELECT</command> command.  Thus, an
  61    <literal>ON SELECT</literal> rule effectively turns the table into
  62    a view, whose visible contents are the rows returned by the rule's
  63    <command>SELECT</command> command rather than whatever had been
  64    stored in the table (if anything).  It is considered better style
  65    to write a <command>CREATE VIEW</command> command than to create a
  66    real table and define an <literal>ON SELECT</literal> rule for it.
  67   </para>
  68
  69   <para>
  70    You can create the illusion of an updatable view by defining
  71    <literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and
  72    <literal>ON DELETE</literal> rules (or any subset of those that's
  73    sufficient for your purposes) to replace update actions on the view
  74    with appropriate updates on other tables.  If you want to support
  75    <command>INSERT RETURNING</> and so on, then be sure to put a suitable
  76    <literal>RETURNING</> clause into each of these rules.
  77   </para>
  78
  79   <para>
  80    There is a catch if you try to use conditional rules for view
  81    updates: there <emphasis>must</> be an unconditional
  82    <literal>INSTEAD</literal> rule for each action you wish to allow
  83    on the view.  If the rule is conditional, or is not
  84    <literal>INSTEAD</literal>, then the system will still reject
  85    attempts to perform the update action, because it thinks it might
  86    end up trying to perform the action on the dummy table of the view
  87    in some cases.  If you want to handle all the useful cases in
  88    conditional rules, add an unconditional <literal>DO
  89    INSTEAD NOTHING</literal> rule to ensure that the system
  90    understands it will never be called on to update the dummy table.
  91    Then make the conditional rules non-<literal>INSTEAD</literal>; in
  92    the cases where they are applied, they add to the default
  93    <literal>INSTEAD NOTHING</literal> action.  (This method does not
  94    currently work to support <literal>RETURNING</> queries, however.)
  95   </para>
  96  </refsect1>
  97
  98  <refsect1>
  99   <title>Parameters</title>
 100
 101   <variablelist>
 102    <varlistentry>
 103     <term><replaceable class="parameter">name</replaceable></term>
 104     <listitem>
 105      <para>
 106       The name of a rule to create.  This must be distinct from the
 107       name of any other rule for the same table.  Multiple rules on
 108       the same table and same event type are applied in alphabetical
 109       name order.
 110      </para>
 111     </listitem>
 112    </varlistentry>
 113
 114    <varlistentry>
 115     <term><replaceable class="parameter">event</replaceable></term>
 116     <listitem>
 117      <para>
 118       The event is one of <literal>SELECT</literal>,
 119       <literal>INSERT</literal>, <literal>UPDATE</literal>, or
 120       <literal>DELETE</literal>.
 121      </para>
 122     </listitem>
 123    </varlistentry>
 124
 125    <varlistentry>
 126     <term><replaceable class="parameter">table</replaceable></term>
 127     <listitem>
 128      <para>
 129       The name (optionally schema-qualified) of the table or view the
 130       rule applies to.
 131      </para>
 132     </listitem>
 133    </varlistentry>
 134
 135    <varlistentry>
 136     <term><replaceable class="parameter">condition</replaceable></term>
 137     <listitem>
 138      <para>
 139       Any <acronym>SQL</acronym> conditional expression (returning
 140       <type>boolean</type>).  The condition expression cannot refer
 141       to any tables except <literal>NEW</> and <literal>OLD</>, and
 142       cannot contain aggregate functions.
 143      </para>
 144     </listitem>
 145    </varlistentry>
 146
 147    <varlistentry>
 148     <term><option>INSTEAD</option></term>
 149     <listitem>
 150      <para>
 151       <literal>INSTEAD</literal> indicates that the commands should be
 152       executed <emphasis>instead of</> the original command.
 153      </para>
 154     </listitem>
 155    </varlistentry>
 156
 157    <varlistentry>
 158     <term><option>ALSO</option></term>
 159     <listitem>
 160      <para>
 161       <literal>ALSO</literal> indicates that the commands should be
 162       executed <emphasis>in addition to</emphasis> the original
 163       command.
 164      </para>
 165
 166      <para>
 167       If neither <literal>ALSO</literal> nor
 168       <literal>INSTEAD</literal> is specified, <literal>ALSO</literal>
 169       is the default.
 170      </para>
 171     </listitem>
 172    </varlistentry>
 173
 174    <varlistentry>
 175     <term><replaceable class="parameter">command</replaceable></term>
 176     <listitem>
 177      <para>
 178       The command or commands that make up the rule action.  Valid
 179       commands are <command>SELECT</command>,
 180       <command>INSERT</command>, <command>UPDATE</command>,
 181       <command>DELETE</command>, or <command>NOTIFY</command>.
 182      </para>
 183     </listitem>
 184    </varlistentry>
 185   </variablelist>
 186
 187   <para>
 188    Within <replaceable class="parameter">condition</replaceable> and
 189    <replaceable class="parameter">command</replaceable>, the special
 190    table names <literal>NEW</literal> and <literal>OLD</literal> can
 191    be used to refer to values in the referenced table.
 192    <literal>NEW</literal> is valid in <literal>ON INSERT</literal> and
 193    <literal>ON UPDATE</literal> rules to refer to the new row being
 194    inserted or updated.  <literal>OLD</literal> is valid in
 195    <literal>ON UPDATE</literal> and <literal>ON DELETE</literal> rules
 196    to refer to the existing row being updated or deleted.
 197   </para>
 198  </refsect1>
 199
 200  <refsect1>
 201   <title>Notes</title>
 202
 203   <para>
 204    You must be the owner of a table to create or change rules for it.
 205   </para>
 206
 207   <para>
 208    In a rule for <literal>INSERT</literal>, <literal>UPDATE</literal>, or
 209    <literal>DELETE</literal> on a view, you can add a <literal>RETURNING</>
 210    clause that emits the view's columns.  This clause will be used to compute
 211    the outputs if the rule is triggered by an <command>INSERT RETURNING</>,
 212    <command>UPDATE RETURNING</>, or <command>DELETE RETURNING</> command
 213    respectively.  When the rule is triggered by a command without
 214    <literal>RETURNING</>, the rule's <literal>RETURNING</> clause will be
 215    ignored.  The current implementation allows only unconditional
 216    <literal>INSTEAD</> rules to contain <literal>RETURNING</>; furthermore
 217    there can be at most one <literal>RETURNING</> clause among all the rules
 218    for the same event.  (This ensures that there is only one candidate
 219    <literal>RETURNING</> clause to be used to compute the results.)
 220    <literal>RETURNING</> queries on the view will be rejected if
 221    there is no <literal>RETURNING</> clause in any available rule.
 222   </para>
 223
 224   <para>
 225    It is very important to take care to avoid circular rules.  For
 226    example, though each of the following two rule definitions are
 227    accepted by <productname>PostgreSQL</productname>, the
 228    <command>SELECT</command> command would cause
 229    <productname>PostgreSQL</productname> to report an error because
 230    of recursive expansion of a rule:
 231
 232 <programlisting>
 233 CREATE RULE "_RETURN" AS
 234     ON SELECT TO t1
 235     DO INSTEAD
 236         SELECT * FROM t2;
 237
 238 CREATE RULE "_RETURN" AS
 239     ON SELECT TO t2
 240     DO INSTEAD
 241         SELECT * FROM t1;
 242
 243 SELECT * FROM t1;
 244 </programlisting>
 245   </para>
 246
 247   <para>
 248    Presently, if a rule action contains a <command>NOTIFY</command>
 249    command, the <command>NOTIFY</command> command will be executed
 250    unconditionally, that is, the <command>NOTIFY</command> will be
 251    issued even if there are not any rows that the rule should apply
 252    to.  For example, in:
 253 <programlisting>
 254 CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;
 255
 256 UPDATE mytable SET name = 'foo' WHERE id = 42;
 257 </programlisting>
 258    one <command>NOTIFY</command> event will be sent during the
 259    <command>UPDATE</command>, whether or not there are any rows that
 260    match the condition <literal>id = 42</literal>.  This is an
 261    implementation restriction that might be fixed in future releases.
 262   </para>
 263  </refsect1>
 264
 265  <refsect1>
 266   <title>Compatibility</title>
 267
 268   <para>
 269    <command>CREATE RULE</command> is a
 270    <productname>PostgreSQL</productname> language extension, as is the
 271    entire query rewrite system.
 272   </para>
 273  </refsect1>
 274 </refentry>