doc/src/sgml/ref/create_rule.sgml

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