On 2011-02-24 6:40 PM, I wrote:
I am planning on working on the documentation this weekend.
And here's my attempt. The language is a bit poor at some places but I
can't think of anything better.
I tried to be more strict about using "subquery" when talking about
WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book.
Regards,
Marko Tiikkaja
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 1526,1532 **** SELECT <replaceable>select_list</replaceable> FROM
<replaceable>table_expression
<sect1 id="queries-with">
! <title><literal>WITH</literal> Queries (Common Table Expressions)</title>
<indexterm zone="queries-with">
<primary>WITH</primary>
--- 1526,1532 ----
<sect1 id="queries-with">
! <title><literal>WITH</literal> Statements (Common Table Expressions)</title>
<indexterm zone="queries-with">
<primary>WITH</primary>
***************
*** 1539,1549 **** SELECT <replaceable>select_list</replaceable> FROM
<replaceable>table_expression
</indexterm>
<para>
! <literal>WITH</> provides a way to write subqueries for use in a larger
! query. The subqueries, which are often referred to as Common Table
Expressions or <acronym>CTE</acronym>s, can be thought of as defining
! temporary tables that exist just for this query. One use of this feature
! is to break down complicated queries into simpler parts. An example is:
<programlisting>
WITH regional_sales AS (
--- 1539,1559 ----
</indexterm>
<para>
! <literal>WITH</> provides a way to write auxiliary statements for use in a
! larger query. These statements, which are often referred to as Common
Table
Expressions or <acronym>CTE</acronym>s, can be thought of as defining
! temporary tables that exist just for this query.
! </para>
!
! <sect2 id="subqueries-with">
! <title>SELECT Queries</title>
!
! <indexterm>
! <primary>*</primary>
! </indexterm>
! <para>
! One use of this feature is to break down complicated queries into simpler
! parts. An example is:
<programlisting>
WITH regional_sales AS (
***************
*** 1806,1811 **** SELECT n FROM t LIMIT 100;
--- 1816,1917 ----
In each case it effectively provides temporary table(s) that can
be referred to in the main command.
</para>
+ </sect2>
+ <sect2 id="modifying-with">
+ <title>Data-Modifying Statements</title>
+
+ <indexterm>
+ <primary>*</primary>
+ </indexterm>
+
+ <para>
+ You can also use data-modifying statements <command>INSERT</>,
+ <command>UPDATE</> and <command>DELETE</> in <literal>WITH</>. This
allows
+ you to perform many different operations in the same query. An example
is:
+
+ <programlisting>
+ WITH moved_rows AS (
+ DELETE FROM ONLY products
+ WHERE
+ "date" >= '2010-10-01' AND
+ "date" < '2010-11-01'
+ RETURNING *
+ )
+ INSERT INTO products_log
+ SELECT * FROM moved_rows;
+ </programlisting>
+
+ which moves rows from "products" to "products_log". In the example above,
+ the <literal>WITH</> clause is attached to the <command>INSERT</>, not the
+ <command>SELECT</>. This is important, because data-modifying statements
+ are not allowed in <literal>WITH</> clauses which are not attached to the
+ top level statement. However, normal <literal>WITH</> visibility rules
+ apply: it is possible to refer to a data-modifying <literal>WITH</> from a
+ subquery.
+ </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</>:
+
+ <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>
+
+ The above query would remove all direct and indirect subparts of a
product.
+ </para>
+
+ <para>
+ The execution of data-modifying statements in <literal>WITH</> is
+ interleaved with the main plan, and the order in which the statements
+ are executed is arbitrary. The changes made by data-modifying statements
+ are not visible to the query.
+ </para>
+ <important>
+ <para>
+ Trying to update the same row twice in a single command is not supported.
+ Only one of the modifications takes place, but it is not easy (and
+ sometimes not possible) to reliably predict which one. This also applies
+ to deleting a row that was already updated in the same command; only the
+ update is performed. You should generally avoid trying to modify a
single
+ row twice in a single command.
+ </para>
+ </important>
+
+ <para>
+ Data-modifying statements are executed exactly once, and always to
+ completion. If a <literal>WITH</> containing a data-modifying statement
+ is not referred to in the query, it is possible to omit the
+ <literal>RETURNING</> clause:
+
+ <programlisting>
+ WITH t AS (
+ DELETE FROM foo
+ )
+ DELETE FROM bar;
+ </programlisting>
+
+ The example above would remove all rows from tables "foo" and "bar". The
+ number of affected rows returned to the client would only include rows
+ removed from "bar".
+ </para>
+
+ <para>
+ Any table used as the target of a data-modifying statement in
+ <literal>WITH</> must not contain a conditional rule, an <literal>ALSO</>
+ rule or an <literal>INSTEAD</> rule with multiple statements.
+ </para>
+
+ </sect2>
</sect1>
*** a/doc/src/sgml/ref/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***************
*** 89,95 **** DELETE FROM [ ONLY ] <replaceable
class="PARAMETER">table</replaceable> [ [ AS ]
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! subqueries that can be referenced by name in the <command>DELETE</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
--- 89,95 ----
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! statements whose results can be referenced by name in the
<command>DELETE</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 90,96 **** INSERT INTO <replaceable class="PARAMETER">table</replaceable> [
( <replaceable
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! subqueries that can be referenced by name in the <command>INSERT</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
--- 90,96 ----
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! statements whose results can be referenced by name in the
<command>INSERT</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 58,64 **** SELECT [ ALL | DISTINCT [ ON ( <replaceable
class="parameter">expression</replac
<phrase>and <replaceable class="parameter">with_query</replaceable>
is:</phrase>
! <replaceable class="parameter">with_query_name</replaceable> [ (
<replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS (
<replaceable class="parameter">select</replaceable> )
TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ *
] | <replaceable class="parameter">with_query_name</replaceable> }
</synopsis>
--- 58,64 ----
<phrase>and <replaceable class="parameter">with_query</replaceable>
is:</phrase>
! <replaceable class="parameter">with_query_name</replaceable> [ (
<replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS (
<replaceable class="parameter">select</replaceable> | <replaceable
class="parameter">insert</replaceable> | <replaceable
class="parameter">update</replaceable> | <replaceable
class="parameter">delete</replaceable> )
TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ *
] | <replaceable class="parameter">with_query_name</replaceable> }
</synopsis>
***************
*** 206,233 **** TABLE { [ ONLY ] <replaceable
class="parameter">table_name</replaceable> [ * ] |
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! subqueries that can be referenced by name in the primary query.
! The subqueries effectively act as temporary tables or views
! for the duration of the primary query.
</para>
<para>
A name (without schema qualification) must be specified for each
! <literal>WITH</literal> query. Optionally, a list of column names
can be specified; if this is omitted,
! the column names are inferred from the subquery.
</para>
<para>
If <literal>RECURSIVE</literal> is specified, it allows a
! subquery to reference itself by name. Such a subquery must have
! the form
<synopsis>
<replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL |
DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
</synopsis>
where the recursive self-reference must appear on the right-hand
side of the <literal>UNION</>. Only one recursive self-reference
! is permitted per query.
</para>
<para>
--- 206,241 ----
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! read-only (<literal>SELECT</literal>) or data-modifying statements whose
! results can be referenced by name in the primary query. These statements
! effectively act as temporary tables or views for the duration of the
! primary query. Currently only <literal>SELECT</literal>,
! <literal>INSERT</literal>, <literal>UPDATE</literal> and
! <literal>DELETE</literal> statements are supported in
! <literal>WITH</literal>.
</para>
<para>
A name (without schema qualification) must be specified for each
! <literal>WITH</literal> statement. Optionally, a list of column names
can be specified; if this is omitted,
! the column names are inferred from the statement.
</para>
<para>
If <literal>RECURSIVE</literal> is specified, it allows a
! <literal>SELECT</literal> subquery to reference itself by name. Such a
! subquery must have the form
<synopsis>
<replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL |
DISTINCT ] <replaceable class="parameter">recursive_term</replaceable>
</synopsis>
where the recursive self-reference must appear on the right-hand
side of the <literal>UNION</>. Only one recursive self-reference
! is permitted per query. Recursive data-modifying statements are not
! supported, but you can use the results of a recursive
! <literal>SELECT</literal> query in
! a data-modifying statement. See <xref linkend="queries-with"> for
! an example.
</para>
<para>
***************
*** 241,247 **** TABLE { [ ONLY ] <replaceable
class="parameter">table_name</replaceable> [ * ] |
</para>
<para>
! A useful property of <literal>WITH</literal> queries is that they
are evaluated only once per execution of the primary query,
even if the primary query refers to them more than once.
</para>
--- 249,255 ----
</para>
<para>
! A useful property of <literal>WITH</literal> statements is that they
are evaluated only once per execution of the primary query,
even if the primary query refers to them more than once.
</para>
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 85,91 **** UPDATE [ ONLY ] <replaceable
class="PARAMETER">table</replaceable> [ [ AS ] <rep
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! subqueries that can be referenced by name in the <command>UPDATE</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
--- 85,91 ----
<listitem>
<para>
The <literal>WITH</literal> clause allows you to specify one or more
! statements whose results can be referenced by name in the
<command>UPDATE</>
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
for details.
</para>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
