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) <> s.sl_avail;
</programlisting>

    After that the rule system runs out of rules and returns the
    generated query trees.
   </para>

   <para>
    So we end up with two final query trees that are equivalent to the
    <acronym>SQL</acronym> statements:

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

    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.
</para>

<para>
    There is a little detail that's a bit ugly. Looking at the two
    queries, it turns out that the <literal>shoelace_data</literal>
    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
    <command>INSERT</command> will be

<literallayout class="monospaced">
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
</literallayout>

    while omitting the extra range table entry would result in a

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

    which produces exactly the same entries in the log table.  Thus,
    the rule system caused one extra scan on the table
    <literal>shoelace_data</literal> that is absolutely not
    necessary. And the same redundant scan is done once more in the
    <command>UPDATE</command>. But it was a really hard job to make
    that all possible at all.
</para>

<para>
    Now we make a final demonstration of the
    <productname>PostgreSQL</productname> rule system and its power.
    Say you add some shoelaces with extraordinary colors to your
    database:

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

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

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

    Its output is:

<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