<literal>MATERIALIZED</literal> to force separate calculation
   of the <literal>WITH</literal> query, or by specifying <literal>NOT
   MATERIALIZED</literal> to force it to be merged into the parent query.
   The latter choice risks duplicate computation of
   the <literal>WITH</literal> query, but it can still give a net savings if
   each usage of the <literal>WITH</literal> query needs only a small part
   of the <literal>WITH</literal> query's full output.
  </para>

  <para>
   A simple example of these rules is
<programlisting>
WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;
</programlisting>
   This <literal>WITH</literal> query will be folded, producing the same
   execution plan as
<programlisting>
SELECT * FROM big_table WHERE key = 123;
</programlisting>
   In particular, if there's an index on <structfield>key</structfield>,
   it will probably be used to fetch just the rows having <literal>key =
   123</literal>.  On the other hand, in
<programlisting>
WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
</programlisting>
   the <literal>WITH</literal> query will be materialized, producing a
   temporary copy of <structname>big_table</structname> that is then
   joined with itself &mdash; without benefit of any index.  This query
   will be executed much more efficiently if written as
<programlisting>
WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
</programlisting>
   so that the parent query's restrictions can be applied directly
   to scans of <structname>big_table</structname>.
  </para>

  <para>
   An example where <literal>NOT MATERIALIZED</literal> could be
   undesirable is
<programlisting>
WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
</programlisting>
   Here, materialization of the <literal>WITH</literal> query ensures
   that <function>very_expensive_function</function> is evaluated only
   once per table row, not twice.
  </para>

  <para>
   The examples above only show <literal>WITH</literal> being used with
   <command>SELECT</command>, but it can be attached in the same way to
   <command>INSERT</command>, <command>UPDATE</command>,
   <command>DELETE</command>, or <command>MERGE</command>.
   In each case it effectively provides temporary table(s) that can
   be referred to in the main command.
  </para>
 </sect2>

 <sect2 id="queries-with-modifying">
   <title>Data-Modifying Statements in <literal>WITH</literal></title>

   <para>
    You can use data-modifying statements (<command>INSERT</command>,
    <command>UPDATE</command>, <command>DELETE</command>, or
    <command>MERGE</command>) in <literal>WITH</literal>.  This
    allows you to perform several different operations in the same query.
    An example is:

<programlisting>
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" &gt;= '2010-10-01' AND
        "date" &lt; '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
</programlisting>

    This query effectively moves rows from <structname>products</structname> to
    <structname>products_log</structname>.  The <command>DELETE</command> in <literal>WITH</literal>
    deletes the specified rows from <structname>products</structname>, returning their
    contents by means of its <literal>RETURNING</literal> clause; and then the
    primary query reads that output and inserts it into
    <structname>products_log</structname>.
   </para>

   <para>
    A fine point of the above example is that the <literal>WITH</literal> clause is
    attached to the <command>INSERT</command>, not the sub-<command>SELECT</command> within
    the <command>INSERT</command>.  This is necessary because data-modifying
    statements are only allowed in <literal>WITH</literal> clauses that are attached
    to