DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '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 the top-level statement.  However, normal <literal>WITH</literal> visibility
    rules apply, so it is possible to refer to the <literal>WITH</literal>
    statement's output from the sub-<command>SELECT</command>.
   </para>

   <para>
    Data-modifying statements in <literal>WITH</literal> usually have
    <literal>RETURNING</literal> clauses (see <xref linkend="dml-returning"/>),
    as shown in the example above.
    It is the output of the <literal>RETURNING</literal> clause, <emphasis>not</emphasis> the
    target table of the data-modifying statement, that forms the temporary
    table that can be referred to by the rest of the query.  If a
    data-modifying statement in <literal>WITH</literal> lacks a <literal>RETURNING</literal>
    clause, then it forms no temporary table and cannot be referred to in
    the rest of the query.  Such a statement will be executed nonetheless.
    A not-particularly-useful example is:

<programlisting>
WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;
</programlisting>

    This example would remove all rows from tables <structname>foo</structname> and
    <structname>bar</structname>.  The number of affected rows reported to the client
    would only include rows removed from <structname>bar</structname>.
   </para>

   <para>
    Recursive self-references in data-modifying statements are not
    allowed.  In some cases it is possible to work around this limitation by
    referring to the output of a recursive <literal>WITH</literal>, for example:

<programlisting>
WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);
</programlisting>