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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers