rules.sgml

PostgreSQL7.4.6 for Linux

</Para>

<Para>
    To change this, we can define rules that modify the behavior of
    these kinds of commands. This is the topic of the next section.
</Para>
</Sect2>

</Sect1>

<Sect1 id="rules-update">
<Title>Rules on <command>INSERT</>, <command>UPDATE</>, and <command>DELETE</></Title>

<indexterm zone="rules-update">
 <primary>rule</primary>
 <secondary sortas="INSERT">for INSERT</secondary>
</indexterm>

<indexterm zone="rules-update">
 <primary>rule</primary>
 <secondary sortas="UPDATE">for UPDATE</secondary>
</indexterm>

<indexterm zone="rules-update">
 <primary>rule</primary>
 <secondary sortas="DELETE">for DELETE</secondary>
</indexterm>

<Para>
    Rules that are defined on <command>INSERT</>, <command>UPDATE</>,
    and <command>DELETE</> are significantly different from the view rules
    described in the previous section. First, their <command>CREATE
    RULE</command> command allows more:

    <ItemizedList>
        <ListItem>
	<Para>
	    They are allowed to have no action.
	</Para>
	</ListItem>

        <ListItem>
	<Para>
	    They can have multiple actions.
	</Para>
	</ListItem>

        <ListItem>
	<Para>
	    They can be <literal>INSTEAD</> or not.
	</Para>
	</ListItem>

        <ListItem>
	<Para>
	    The pseudorelations <literal>NEW</> and <literal>OLD</> become useful.
	</Para>
	</ListItem>

        <ListItem>
	<Para>
	    They can have rule qualifications.
	</Para>
	</ListItem>
    </ItemizedList>

    Second, they don't modify the query tree in place. Instead they
    create zero or more new query trees and can throw away the
    original one.
</Para>

<Sect2>
<Title>How Update Rules Work</Title>

<Para>
    Keep the syntax

<ProgramListing>
CREATE RULE <replaceable>rule_name</> AS ON <replaceable>event</>
    TO <replaceable>object</> [WHERE <replaceable>rule_qualification</>]
    DO [INSTEAD] [<replaceable>action</> | (<replaceable>actions</>) | NOTHING];
</ProgramListing>

    in mind.
    In the following, <firstterm>update rules</> means rules that are defined
    on <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</>.
</Para>

<Para>
    Update rules get applied by the rule system when the result
    relation and the command type of a query tree are equal to the
    object and event given in the <command>CREATE RULE</command> command.
    For update rules, the rule system creates a list of query trees.
    Initially the query-tree list is empty.
    There can be zero (<literal>NOTHING</> key word), one, or multiple actions.
    To simplify, we will look at a rule with one action. This rule
    can have a qualification or not and it can be <literal>INSTEAD</> or not.
</Para>

<Para>
    What is a rule qualification? It is a restriction that tells
    when the actions of the rule should be done and when not. This
    qualification can only reference the pseudorelations <literal>NEW</> and/or <literal>OLD</>,
    which basically represent the relation that was given as object (but with a
    special meaning).
</Para>

   <para>
    So we have four cases that produce the following query trees for
    a one-action rule.

    <variablelist>
     <varlistentry>
      <term>No qualification and not <literal>INSTEAD</></term>
      <listitem>
       <para>
        the query tree from the rule action with the original query
        tree's qualification added
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>No qualification but <literal>INSTEAD</></term>
      <listitem>
       <para>
        the query tree from the rule action with the original query
        tree's qualification added
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>Qualification given and not <literal>INSTEAD</></term>
      <listitem>
       <para>
        the query tree from the rule action with the rule
        qualification and the original query tree's qualification
        added
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>Qualification given and <literal>INSTEAD</></term>
      <listitem>
       <para>
        the query tree from the rule action with the rule
        qualification and the original query tree's qualification; and
        the original query tree with the negated rule qualification
        added
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

    Finally, if the rule is not <literal>INSTEAD</>, the unchanged original query tree is
    added to the list. Since only qualified <literal>INSTEAD</> rules already add the
    original query tree, we end up with either one or two output query trees
    for a rule with one action.
</Para>

<Para>
    For <literal>ON INSERT</> rules, the original query (if not suppressed by <literal>INSTEAD</>)
    is done before any actions added by rules.  This allows the actions to
    see the inserted row(s).  But for <literal>ON UPDATE</> and <literal>ON
    DELETE</> rules, the original query is done after the actions added by rules.
    This ensures that the actions can see the to-be-updated or to-be-deleted
    rows; otherwise, the actions might do nothing because they find no rows
    matching their qualifications.
</Para>

<Para>
    The query trees generated from rule actions are thrown into the
    rewrite system again, and maybe more rules get applied resulting
    in more or less query trees.
    So the query trees in the rule actions must have either a different command type
    or a different result relation, otherwise, this recursive process will end up in a loop.
    There is a fixed recursion limit of currently 100 iterations.
    If after 100 iterations there are still update rules to apply, the
    rule system assumes a loop over multiple rule definitions and reports
    an error.
</Para>

<Para>
    The query trees found in the actions of the
    <structname>pg_rewrite</structname> system catalog are only
    templates. Since they can reference the range-table entries for
    <literal>NEW</> and <literal>OLD</>, some substitutions have to be made before they can be
    used. For any reference to <literal>NEW</>, the target list of the original
    query is searched for a corresponding entry. If found, that
    entry's expression replaces the reference. Otherwise, <literal>NEW</> means the
    same as <literal>OLD</> (for an <command>UPDATE</command>) or is replaced by
    a null value (for an <command>INSERT</command>). Any reference to <literal>OLD</> is
    replaced by a reference to the range-table entry that is the
    result relation.
</Para>

<Para>
    After the system is done applying update rules, it applies view rules to the
    produced query tree(s).  Views cannot insert new update actions so
    there is no need to apply update rules to the output of view rewriting.
</Para>

<Sect3>
<Title>A First Rule Step by Step</Title>

<Para>
    Say we want to trace changes to the <literal>sl_avail</> column in the
    <literal>shoelace_data</literal> relation. So we set up a log table
    and a rule that conditionally writes a log entry when an
    <command>UPDATE</command> is performed on
    <literal>shoelace_data</literal>.

<ProgramListing>
CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail &lt;&gt; OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );
</ProgramListing>
</Para>

<Para>
    Now someone does:

<ProgramListing>
UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
</ProgramListing>

    and we look at the log table:

<ProgramListing>
SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who | log_when                        
---------+----------+---------+----------------------------------
 sl7     |        6 | Al      | Tue Oct 20 16:14:45 1998 MET DST
(1 row)
</ProgramListing>
   </para>

   <para>
    That's what we expected. What happened in the background is the following.
    The parser created the query tree

<ProgramListing>
UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';
</ProgramListing>

    There is a rule <literal>log_shoelace</literal> that is <literal>ON UPDATE</> with the rule
    qualification expression

<ProgramListing>
NEW.sl_avail &lt;&gt; OLD.sl_avail
</ProgramListing>

    and the action

<ProgramListing>
INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*;
</ProgramListing>

    (This looks a little strange since you can't normally write
    <literal>INSERT ... VALUES ... FROM</>.  The <literal>FROM</>
    clause here is just to indicate that there are range-table entries
    in the query tree for <literal>*NEW*</> and <literal>*OLD*</>.
    These are needed so that they can be referenced by variables in
    the <command>INSERT</command> command's query tree.)
</Para>

<Para>
    The rule is a qualified non-<literal>INSTEAD</> rule, so the rule system
    has to return two query trees: the modified rule action and the original
    query tree. In step 1, the range table of the original query is
    incorporated into the rule's action query tree. This results in:

<ProgramListing>
INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       <emphasis>shoelace_data shoelace_data</emphasis>;
</ProgramListing>

    In step 2, the rule qualification is added to it, so the result set
    is restricted to rows where <literal>sl_avail</> changes:

<ProgramListing>
INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 <emphasis>WHERE *NEW*.sl_avail &lt;&gt; *OLD*.sl_avail</emphasis>;
</ProgramListing>

    (This looks even stranger, since <literal>INSERT ... VALUES</> doesn't have
    a <literal>WHERE</> clause either, but the planner and executor will have no
    difficulty with it.  They need to support this same functionality
    anyway for <literal>INSERT ... SELECT</>.)
   </para>

   <para>
    In step 3, the original query tree's qualification is added,
    restricting the result set further to only the rows that would have been touched
    by the original query:

<ProgramListing>
INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE *NEW*.sl_avail &lt;&gt; *OLD*.sl_avail
   <emphasis>AND shoelace_data.sl_name = 'sl7'</emphasis>;
</ProgramListing>
   </para>

   <para>
    Step 4 replaces references to <literal>NEW</> by the target list entries from the
    original query tree or by the matching variable references
    from the result relation:

<ProgramListing>
INSERT INTO shoelace_log VALUES (
       <emphasis>shoelace_data.sl_name</emphasis>, <emphasis>6</emphasis>,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE <emphasis>6</emphasis> &lt;&gt; *OLD*.sl_avail
   AND shoelace_data.sl_name = 'sl7';
</ProgramListing>

   </para>

   <para>
    Step 5 changes <literal>OLD</> references into result relation references:

<ProgramListing>
INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE 6 &lt;&gt; <emphasis>shoelace_data.sl_avail</emphasis>
   AND shoelace_data.sl_name = 'sl7';
</ProgramListing>
   </para>

   <para>
    That's it.  Since the rule is not <literal>INSTEAD</>, we also output the
    original query tree.  In short, the output from the rule system
    is a list of two query trees that correspond to these statements:

<ProgramListing>
INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 &lt;&gt; shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

UPDATE shoelace_data SET sl_avail = 6
 WHERE sl_name = 'sl7';
</ProgramListing>

    These are executed in this order, and that is exactly what
    the rule was meant to do.
   </para>

   <para>
    The substitutions and the added qualifications
    ensure that, if the original query would be, say,

<ProgramListing>
UPDATE shoelace_data SET sl_color = 'green'
 WHERE sl_name = 'sl7';
</ProgramListing>

    no log entry would get written.  In that case, the original query
    tree does not contain a target list entry for
    <literal>sl_avail</>, so <literal>NEW.sl_avail</> will get
    replaced by <literal>shoelace_data.sl_avail</>.  Thus, the extra
    command generated by the rule is

<ProgramListing>
INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, <emphasis>shoelace_data.sl_avail</emphasis>,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE <emphasis>shoelace_data.sl_avail</emphasis> &lt;&gt; shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';
</ProgramListing>

    and that qualification will never be true.
   </para>