File: rules-update.html

package info (click to toggle)
pgadmin3 1.4.3-2
links: PTS
area: main
in suites: etch, etch-m68k
size: 29,796 kB
ctags: 10,758
sloc: cpp: 55,356; sh: 6,164; ansic: 1,520; makefile: 576; sql: 482; xml: 100; perl: 18
file content (729 lines) | stat: -rw-r--r-- 31,091 bytes
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>34.3.Rules on INSERT, UPDATE, and DELETE</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="rules.html" title="Chapter34.The Rule System">
<link rel="prev" href="rules-views.html" title="34.2.Views and the Rule System">
<link rel="next" href="rules-privileges.html" title="34.4.Rules and Privileges">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="rules-update"></a>34.3.Rules on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code></h2></div></div></div>
<a name="id717489"></a><a name="id717506"></a><a name="id717521"></a><p>    Rules that are defined on <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
    and <code class="command">DELETE</code> are significantly different from the view rules
    described in the previous section. First, their <code class="command">CREATE
    RULE</code> command allows more:

    </p>
<div class="itemizedlist"><ul type="disc">
<li><p>            They are allowed to have no action.
        </p></li>
<li><p>            They can have multiple actions.
        </p></li>
<li><p>            They can be <code class="literal">INSTEAD</code> or <code class="literal">ALSO</code> (default).
        </p></li>
<li><p>            The pseudorelations <code class="literal">NEW</code> and <code class="literal">OLD</code> become useful.
        </p></li>
<li><p>            They can have rule qualifications.
        </p></li>
</ul></div>
<p>

    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.</p>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="id717622"></a>34.3.1.How Update Rules Work</h3></div></div></div>
<p>    Keep the syntax

</p>
<pre class="programlisting">CREATE [ OR REPLACE ] RULE <em class="replaceable"><code>name</code></em> AS ON <em class="replaceable"><code>event</code></em>
    TO <em class="replaceable"><code>table</code></em> [ WHERE <em class="replaceable"><code>condition</code></em> ]
    DO [ ALSO | INSTEAD ] { NOTHING | <em class="replaceable"><code>command</code></em> | ( <em class="replaceable"><code>command</code></em> ; <em class="replaceable"><code>command</code></em> ... ) }</pre>
<p>

    in mind.
    In the following, <em class="firstterm">update rules</em> means rules that are defined
    on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>.</p>
<p>    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 <code class="command">CREATE RULE</code> command.
    For update rules, the rule system creates a list of query trees.
    Initially the query-tree list is empty.
    There can be zero (<code class="literal">NOTHING</code> 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 <code class="literal">INSTEAD</code> or <code class="literal">ALSO</code> (default).</p>
<p>    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 <code class="literal">NEW</code> and/or <code class="literal">OLD</code>,
    which basically represent the relation that was given as object (but with a
    special meaning).</p>
<p>    So we have four cases that produce the following query trees for
    a one-action rule.

    </p>
<div class="variablelist"><dl>
<dt><span class="term">No qualification and <code class="literal">ALSO</code></span></dt>
<dd><p>        the query tree from the rule action with the original query
        tree's qualification added
       </p></dd>
<dt><span class="term">No qualification but <code class="literal">INSTEAD</code></span></dt>
<dd><p>        the query tree from the rule action with the original query
        tree's qualification added
       </p></dd>
<dt><span class="term">Qualification given and <code class="literal">ALSO</code></span></dt>
<dd><p>        the query tree from the rule action with the rule
        qualification and the original query tree's qualification
        added
       </p></dd>
<dt><span class="term">Qualification given and <code class="literal">INSTEAD</code></span></dt>
<dd><p>        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
       </p></dd>
</dl></div>
<p>

    Finally, if the rule is <code class="literal">ALSO</code>, the unchanged original query tree is
    added to the list. Since only qualified <code class="literal">INSTEAD</code> rules already add the
    original query tree, we end up with either one or two output query trees
    for a rule with one action.</p>
<p>    For <code class="literal">ON INSERT</code> rules, the original query (if not suppressed by <code class="literal">INSTEAD</code>)
    is done before any actions added by rules.  This allows the actions to
    see the inserted row(s).  But for <code class="literal">ON UPDATE</code> and <code class="literal">ON
    DELETE</code> 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.</p>
<p>    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 a rule's actions must have either a different
    command type or a different result relation than the rule itself is
    on, otherwise this recursive process will end up in an infinite loop.
    (Recursive expansion of a rule will be detected and reported as an
    error.)</p>
<p>    The query trees found in the actions of the
    <code class="structname">pg_rewrite</code> system catalog are only
    templates. Since they can reference the range-table entries for
    <code class="literal">NEW</code> and <code class="literal">OLD</code>, some substitutions have to be made before they can be
    used. For any reference to <code class="literal">NEW</code>, the target list of the original
    query is searched for a corresponding entry. If found, that
    entry's expression replaces the reference. Otherwise, <code class="literal">NEW</code> means the
    same as <code class="literal">OLD</code> (for an <code class="command">UPDATE</code>) or is replaced by
    a null value (for an <code class="command">INSERT</code>). Any reference to <code class="literal">OLD</code> is
    replaced by a reference to the range-table entry that is the
    result relation.</p>
<p>    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.</p>
<div class="sect3" lang="en">
<div class="titlepage"><div><div><h4 class="title">
<a name="id717970"></a>34.3.1.1.A First Rule Step by Step</h4></div></div></div>
<p>    Say we want to trace changes to the <code class="literal">sl_avail</code> column in the
    <code class="literal">shoelace_data</code> relation. So we set up a log table
    and a rule that conditionally writes a log entry when an
    <code class="command">UPDATE</code> is performed on
    <code class="literal">shoelace_data</code>.

</p>
<pre class="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
                                );</pre>
<p>    Now someone does:

</p>
<pre class="programlisting">UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';</pre>
<p>

    and we look at the log table:

</p>
<pre class="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)</pre>
<p>
   </p>
<p>    That's what we expected. What happened in the background is the following.
    The parser created the query tree

</p>
<pre class="programlisting">UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';</pre>
<p>

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

</p>
<pre class="programlisting">NEW.sl_avail &lt;&gt; OLD.sl_avail</pre>
<p>

    and the action

</p>
<pre class="programlisting">INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*;</pre>
<p>

    (This looks a little strange since you can't normally write
    <code class="literal">INSERT ... VALUES ... FROM</code>.  The <code class="literal">FROM</code>
    clause here is just to indicate that there are range-table entries
    in the query tree for <code class="literal">*NEW*</code> and <code class="literal">*OLD*</code>.
    These are needed so that they can be referenced by variables in
    the <code class="command">INSERT</code> command's query tree.)</p>
<p>    The rule is a qualified <code class="literal">ALSO</code> 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:

</p>
<pre class="programlisting">INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       <span><strong class="emphasis">shoelace_data shoelace_data</strong></span>;</pre>
<p>

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

</p>
<pre class="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
 <span><strong class="emphasis">WHERE *NEW*.sl_avail &lt;&gt; *OLD*.sl_avail</strong></span>;</pre>
<p>

    (This looks even stranger, since <code class="literal">INSERT ... VALUES</code> doesn't have
    a <code class="literal">WHERE</code> clause either, but the planner and executor will have no
    difficulty with it.  They need to support this same functionality
    anyway for <code class="literal">INSERT ... SELECT</code>.)
   </p>
<p>    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:

</p>
<pre class="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
   <span><strong class="emphasis">AND shoelace_data.sl_name = 'sl7'</strong></span>;</pre>
<p>
   </p>
<p>    Step 4 replaces references to <code class="literal">NEW</code> by the target list entries from the
    original query tree or by the matching variable references
    from the result relation:

</p>
<pre class="programlisting">INSERT INTO shoelace_log VALUES (
       <span><strong class="emphasis">shoelace_data.sl_name</strong></span>, <span><strong class="emphasis">6</strong></span>,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE <span><strong class="emphasis">6</strong></span> &lt;&gt; *OLD*.sl_avail
   AND shoelace_data.sl_name = 'sl7';</pre>
<p>

   </p>
<p>    Step 5 changes <code class="literal">OLD</code> references into result relation references:

</p>
<pre class="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; <span><strong class="emphasis">shoelace_data.sl_avail</strong></span>
   AND shoelace_data.sl_name = 'sl7';</pre>
<p>
   </p>
<p>    That's it.  Since the rule is <code class="literal">ALSO</code>, 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:

</p>
<pre class="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';</pre>
<p>

    These are executed in this order, and that is exactly what
    the rule was meant to do.
   </p>
<p>    The substitutions and the added qualifications
    ensure that, if the original query would be, say,

</p>
<pre class="programlisting">UPDATE shoelace_data SET sl_color = 'green'
 WHERE sl_name = 'sl7';</pre>
<p>

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

</p>
<pre class="programlisting">INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, <span><strong class="emphasis">shoelace_data.sl_avail</strong></span>,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE <span><strong class="emphasis">shoelace_data.sl_avail</strong></span> &lt;&gt; shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';</pre>
<p>

    and that qualification will never be true.
   </p>
<p>    It will also work if the original query modifies multiple rows. So
    if someone issued the command

</p>
<pre class="programlisting">UPDATE shoelace_data SET sl_avail = 0
 WHERE sl_color = 'black';</pre>
<p>

    four rows in fact get updated (<code class="literal">sl1</code>, <code class="literal">sl2</code>, <code class="literal">sl3</code>, and <code class="literal">sl4</code>).
    But <code class="literal">sl3</code> already has <code class="literal">sl_avail = 0</code>.   In this case, the original
    query trees qualification is different and that results
    in the extra query tree

</p>
<pre class="programlisting">INSERT INTO shoelace_log
SELECT shoelace_data.sl_name, 0,
       current_user, current_timestamp
  FROM shoelace_data
 WHERE 0 &lt;&gt; shoelace_data.sl_avail
   AND <span><strong class="emphasis">shoelace_data.sl_color = 'black'</strong></span>;</pre>
<p>

    being generated by the rule.  This query tree will surely insert
    three new log entries. And that's absolutely correct.</p>
<p>    Here we can see why it is important that the original query tree
    is executed last.  If the <code class="command">UPDATE</code> had been
    executed first, all the rows would have already been set to zero, so the
    logging <code class="command">INSERT</code> would not find any row where
    <code class="literal">0 &lt;&gt; shoelace_data.sl_avail</code>.</p>
</div>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="rules-update-views"></a>34.3.2.Cooperation with Views</h3></div></div></div>
<a name="id718472"></a><p>    A simple way to protect view relations from the mentioned
    possibility that someone can try to run <code class="command">INSERT</code>,
    <code class="command">UPDATE</code>, or <code class="command">DELETE</code> on them is
    to let those query trees get thrown away.  So we create the rules

</p>
<pre class="programlisting">CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
    DO INSTEAD NOTHING;
CREATE RULE shoe_del_protect AS ON DELETE TO shoe
    DO INSTEAD NOTHING;</pre>
<p>

    If someone now tries to do any of these operations on the view
    relation <code class="literal">shoe</code>, the rule system will
    apply these rules. Since the rules have
    no actions and are <code class="literal">INSTEAD</code>, the resulting list of
    query trees will be empty and the whole query will become
    nothing because there is nothing left to be optimized or
    executed after the rule system is done with it.</p>
<p>    A more sophisticated way to use the rule system is to
    create rules that rewrite the query tree into one that
    does the right operation on the real tables. To do that
    on the <code class="literal">shoelace</code> view, we create
    the following rules:

</p>
<pre class="programlisting">CREATE RULE shoelace_ins AS ON INSERT TO shoelace
    DO INSTEAD
    INSERT INTO shoelace_data VALUES (
           NEW.sl_name,
           NEW.sl_avail,
           NEW.sl_color,
           NEW.sl_len,
           NEW.sl_unit
    );

CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
    DO INSTEAD
    UPDATE shoelace_data
       SET sl_name = NEW.sl_name,
           sl_avail = NEW.sl_avail,
           sl_color = NEW.sl_color,
           sl_len = NEW.sl_len,
           sl_unit = NEW.sl_unit
     WHERE sl_name = OLD.sl_name;

CREATE RULE shoelace_del AS ON DELETE TO shoelace
    DO INSTEAD
    DELETE FROM shoelace_data
     WHERE sl_name = OLD.sl_name;</pre>
<p>
   </p>
<p>    Now assume that once in a while, a pack of shoelaces arrives at
    the shop and a big parts list along with it.  But you don't want
    to manually update the <code class="literal">shoelace</code> view every
    time.  Instead we setup two little tables: one where you can
    insert the items from the part list, and one with a special
    trick. The creation commands for these are:

</p>
<pre class="programlisting">CREATE TABLE shoelace_arrive (
    arr_name    text,
    arr_quant   integer
);

CREATE TABLE shoelace_ok (
    ok_name     text,
    ok_quant    integer
);

CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
    DO INSTEAD
    UPDATE shoelace
       SET sl_avail = sl_avail + NEW.ok_quant
     WHERE sl_name = NEW.ok_name;</pre>
<p>

    Now you can fill the table <code class="literal">shoelace_arrive</code> with
    the data from the parts list:

</p>
<pre class="programlisting">SELECT * FROM shoelace_arrive;

 arr_name | arr_quant
----------+-----------
 sl3      |        10
 sl6      |        20
 sl8      |        20
(3 rows)</pre>
<p>

    Take a quick look at the current data:
    
</p>
<pre class="programlisting">SELECT * FROM shoelace;

 sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
----------+----------+----------+--------+---------+-----------
 sl1      |        5 | black    |     80 | cm      |        80
 sl2      |        6 | black    |    100 | cm      |       100
 sl7      |        6 | brown    |     60 | cm      |        60
 sl3      |        0 | black    |     35 | inch    |      88.9
 sl4      |        8 | black    |     40 | inch    |     101.6
 sl8      |        1 | brown    |     40 | inch    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |        0 | brown    |    0.9 | m       |        90
(8 rows)</pre>
<p>

    Now move the arrived shoelaces in:

</p>
<pre class="programlisting">INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;</pre>
<p>

    and check the results:

</p>
<pre class="programlisting">SELECT * FROM shoelace ORDER BY sl_name;

 sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
----------+----------+----------+--------+---------+-----------
 sl1      |        5 | black    |     80 | cm      |        80
 sl2      |        6 | black    |    100 | cm      |       100
 sl7      |        6 | brown    |     60 | cm      |        60
 sl4      |        8 | black    |     40 | inch    |     101.6
 sl3      |       10 | black    |     35 | inch    |      88.9
 sl8      |       21 | brown    |     40 | inch    |     101.6
 sl5      |        4 | brown    |      1 | m       |       100
 sl6      |       20 | brown    |    0.9 | m       |        90
(8 rows)

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who| log_when                        
---------+----------+--------+----------------------------------
 sl7     |        6 | Al     | Tue Oct 20 19:14:45 1998 MET DST
 sl3     |       10 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl6     |       20 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 sl8     |       21 | Al     | Tue Oct 20 19:25:16 1998 MET DST
(4 rows)</pre>
<p>
   </p>
<p>    It's a long way from the one <code class="literal">INSERT ... SELECT</code>
    to these results. And the description of the query-tree
    transformation will be the last in this chapter.  First, there is
    the parser's output

</p>
<pre class="programlisting">INSERT INTO shoelace_ok
SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;</pre>
<p>

    Now the first rule <code class="literal">shoelace_ok_ins</code> is applied and turns this
    into

</p>
<pre class="programlisting">UPDATE shoelace
   SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok *OLD*, shoelace_ok *NEW*,
       shoelace shoelace
 WHERE shoelace.sl_name = shoelace_arrive.arr_name;</pre>
<p>

    and throws away the original <code class="command">INSERT</code> on
    <code class="literal">shoelace_ok</code>.  This rewritten query is passed to
    the rule system again, and the second applied rule
    <code class="literal">shoelace_upd</code> produces

</p>
<pre class="programlisting">UPDATE shoelace_data
   SET sl_name = shoelace.sl_name,
       sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,
       sl_color = shoelace.sl_color,
       sl_len = shoelace.sl_len,
       sl_unit = shoelace.sl_unit
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok *OLD*, shoelace_ok *NEW*,
       shoelace shoelace, shoelace *OLD*,
       shoelace *NEW*, shoelace_data shoelace_data
 WHERE shoelace.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = shoelace.sl_name;</pre>
<p>

    Again it's an <code class="literal">INSTEAD</code> rule and the previous query tree is trashed.
    Note that this query still uses the view <code class="literal">shoelace</code>.
    But the rule system isn't finished with this step, so it continues
    and applies the <code class="literal">_RETURN</code> rule on it, and we get

</p>
<pre class="programlisting">UPDATE shoelace_data
   SET sl_name = s.sl_name,
       sl_avail = s.sl_avail + shoelace_arrive.arr_quant,
       sl_color = s.sl_color,
       sl_len = s.sl_len,
       sl_unit = s.sl_unit
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok *OLD*, shoelace_ok *NEW*,
       shoelace shoelace, shoelace *OLD*,
       shoelace *NEW*, shoelace_data shoelace_data,
       shoelace *OLD*, shoelace *NEW*,
       shoelace_data s, unit u
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name;</pre>
<p>

    Finally, the rule <code class="literal">log_shoelace</code> gets applied,
    producing the extra query tree

</p>
<pre class="programlisting">INSERT INTO shoelace_log
SELECT s.sl_name,
       s.sl_avail + shoelace_arrive.arr_quant,
       current_user,
       current_timestamp
  FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
       shoelace_ok *OLD*, shoelace_ok *NEW*,
       shoelace shoelace, shoelace *OLD*,
       shoelace *NEW*, shoelace_data shoelace_data,
       shoelace *OLD*, shoelace *NEW*,
       shoelace_data s, unit u,
       shoelace_data *OLD*, shoelace_data *NEW*
       shoelace_log shoelace_log
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name
   AND (s.sl_avail + shoelace_arrive.arr_quant) &lt;&gt; s.sl_avail;</pre>
<p>
    
    After that the rule system runs out of rules and returns the 
    generated query trees.
   </p>
<p>    So we end up with two final query trees that are equivalent to the
    <acronym class="acronym">SQL</acronym> statements

</p>
<pre class="programlisting">INSERT INTO shoelace_log
SELECT s.sl_name,
       s.sl_avail + shoelace_arrive.arr_quant,
       current_user,
       current_timestamp
  FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,
       shoelace_data s
 WHERE s.sl_name = shoelace_arrive.arr_name
   AND shoelace_data.sl_name = s.sl_name
   AND s.sl_avail + shoelace_arrive.arr_quant &lt;&gt; s.sl_avail;
           
UPDATE shoelace_data
   SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant
  FROM shoelace_arrive shoelace_arrive,
       shoelace_data shoelace_data,
       shoelace_data s
 WHERE s.sl_name = shoelace_arrive.sl_name
   AND shoelace_data.sl_name = s.sl_name;</pre>
<p>

    The result is that data coming from one relation inserted into another,
    changed into updates on a third, changed into updating
    a fourth plus logging that final update in a fifth
    gets reduced into two queries.</p>
<p>    There is a little detail that's a bit ugly. Looking at the two
    queries, it turns out that the <code class="literal">shoelace_data</code>
    relation appears twice in the range table where it could
    definitely be reduced to one. The planner does not handle it and
    so the execution plan for the rule systems output of the
    <code class="command">INSERT</code> will be

</p>
<pre class="literallayout">Nested Loop
  -&gt;  Merge Join
        -&gt;  Seq Scan
              -&gt;  Sort
                    -&gt;  Seq Scan on s
        -&gt;  Seq Scan
              -&gt;  Sort
                    -&gt;  Seq Scan on shoelace_arrive
  -&gt;  Seq Scan on shoelace_data</pre>
<p>

    while omitting the extra range table entry would result in a

</p>
<pre class="literallayout">Merge Join
  -&gt;  Seq Scan
        -&gt;  Sort
              -&gt;  Seq Scan on s
  -&gt;  Seq Scan
        -&gt;  Sort
              -&gt;  Seq Scan on shoelace_arrive</pre>
<p>

    which produces exactly the same entries in the log table.  Thus,
    the rule system caused one extra scan on the table
    <code class="literal">shoelace_data</code> that is absolutely not
    necessary. And the same redundant scan is done once more in the
    <code class="command">UPDATE</code>. But it was a really hard job to make
    that all possible at all.</p>
<p>    Now we make a final demonstration of the
    <span class="productname">PostgreSQL</span> rule system and its power.
    Say you add some shoelaces with extraordinary colors to your
    database:

</p>
<pre class="programlisting">INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);</pre>
<p>

    We would like to make a view to check which
    <code class="literal">shoelace</code> entries do not fit any shoe in color.
    The view for this is

</p>
<pre class="programlisting">CREATE VIEW shoelace_mismatch AS
    SELECT * FROM shoelace WHERE NOT EXISTS
        (SELECT shoename FROM shoe WHERE slcolor = sl_color);</pre>
<p>

    Its output is

</p>
<pre class="programlisting">SELECT * FROM shoelace_mismatch;

 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
 sl9     |        0 | pink     |     35 | inch    |      88.9
 sl10    |     1000 | magenta  |     40 | inch    |     101.6</pre>
<p>
   </p>
<p>    Now we want to set it up so that mismatching shoelaces that are
    not in stock are deleted from the database.
    To make it a little harder for <span class="productname">PostgreSQL</span>,
    we don't delete it directly. Instead we create one more view

</p>
<pre class="programlisting">CREATE VIEW shoelace_can_delete AS
    SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;</pre>
<p>

    and do it this way:

</p>
<pre class="programlisting">DELETE FROM shoelace WHERE EXISTS
    (SELECT * FROM shoelace_can_delete
             WHERE sl_name = shoelace.sl_name);</pre>
<p>

    <span class="foreignphrase"><em class="foreignphrase">Voil</em></span>:

</p>
<pre class="programlisting">SELECT * FROM shoelace;

 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
 sl1     |        5 | black    |     80 | cm      |        80
 sl2     |        6 | black    |    100 | cm      |       100
 sl7     |        6 | brown    |     60 | cm      |        60
 sl4     |        8 | black    |     40 | inch    |     101.6
 sl3     |       10 | black    |     35 | inch    |      88.9
 sl8     |       21 | brown    |     40 | inch    |     101.6
 sl10    |     1000 | magenta  |     40 | inch    |     101.6
 sl5     |        4 | brown    |      1 | m       |       100
 sl6     |       20 | brown    |    0.9 | m       |        90
(9 rows)</pre>
<p>
   </p>
<p>    A <code class="command">DELETE</code> on a view, with a subquery qualification that
    in total uses 4 nesting/joined views, where one of them
    itself has a subquery qualification containing a view
    and where calculated view columns are used,
    gets rewritten into 
    one single query tree that deletes the requested data
    from a real table.</p>
<p>    There are probably only a few situations out in the real world
    where such a construct is necessary. But it makes you feel
    comfortable that it works.</p>
</div>
</div></body>
</html>