Home • Explore • Blog • CI
☰



postgresql
4th chunk of `doc/src/sgml/ref/create_rule.sgml`
c70556f592e79c90b3f2e94e89d0e962bd73f0867fd76b540000000100000eab
 <command>DELETE</command>, or <command>NOTIFY</command>.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

  <para>
   Within <replaceable class="parameter">condition</replaceable> and
   <replaceable class="parameter">command</replaceable>, the special
   table names <literal>NEW</literal> and <literal>OLD</literal> can
   be used to refer to values in the referenced table.
   <literal>NEW</literal> is valid in <literal>ON INSERT</literal> and
   <literal>ON UPDATE</literal> rules to refer to the new row being
   inserted or updated.  <literal>OLD</literal> is valid in
   <literal>ON UPDATE</literal> and <literal>ON DELETE</literal> rules
   to refer to the existing row being updated or deleted.
  </para>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   You must be the owner of a table to create or change rules for it.
  </para>

  <para>
   In a rule for <literal>INSERT</literal>, <literal>UPDATE</literal>, or
   <literal>DELETE</literal> on a view, you can add a <literal>RETURNING</literal>
   clause that emits the view's columns.  This clause will be used to compute
   the outputs if the rule is triggered by an <command>INSERT RETURNING</command>,
   <command>UPDATE RETURNING</command>, or <command>DELETE RETURNING</command> command
   respectively.  When the rule is triggered by a command without
   <literal>RETURNING</literal>, the rule's <literal>RETURNING</literal> clause will be
   ignored.  The current implementation allows only unconditional
   <literal>INSTEAD</literal> rules to contain <literal>RETURNING</literal>; furthermore
   there can be at most one <literal>RETURNING</literal> clause among all the rules
   for the same event.  (This ensures that there is only one candidate
   <literal>RETURNING</literal> clause to be used to compute the results.)
   <literal>RETURNING</literal> queries on the view will be rejected if
   there is no <literal>RETURNING</literal> clause in any available rule.
  </para>

  <para>
   It is very important to take care to avoid circular rules.  For
   example, though each of the following two rule definitions are
   accepted by <productname>PostgreSQL</productname>, the
   <command>SELECT</command> command would cause
   <productname>PostgreSQL</productname> to report an error because
   of recursive expansion of a rule:

<programlisting>
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;
</programlisting>
  </para>

  <para>
   Presently, if a rule action contains a <command>NOTIFY</command>
   command, the <command>NOTIFY</command> command will be executed
   unconditionally, that is, the <command>NOTIFY</command> will be
   issued even if there are not any rows that the rule should apply
   to.  For example, in:
<programlisting>
CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;

UPDATE mytable SET name = 'foo' WHERE id = 42;
</programlisting>
   one <command>NOTIFY</command> event will be sent during the
   <command>UPDATE</command>, whether or not there are any rows that
   match the condition <literal>id = 42</literal>.  This is an
   implementation restriction that might be fixed in future releases.
  </para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   <command>CREATE RULE</command> is a
   <productname>PostgreSQL</productname> language extension, as is the
   entire query rewrite system.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-alterrule"/></member>
   <member><xref linkend="sql-droprule"/></member>
  </simplelist>
 </refsect1>

</refentry>
Title: Notes on Creating Rules: RETURNING Clauses, Circular Rules, and NOTIFY Behavior
Summary
When creating rules for views, especially for INSERT, UPDATE, or DELETE operations, you can include a RETURNING clause to output the view's columns. This is used when the triggering command includes a RETURNING clause. Only unconditional INSTEAD rules can contain RETURNING, and there can be at most one RETURNING clause for the same event. Be cautious to avoid circular rules that cause recursive expansion. If a rule action includes a NOTIFY command, it will be executed unconditionally, regardless of whether any rows meet the rule's criteria. CREATE RULE is a PostgreSQL extension to SQL.