Hi, Attached is a documentation patch for writeable CTEs.
Most of it is explaining how this feature works in select.sgml. I wasn't sure if that's the right place, but couldn't find a better one. I also wasn't able to find any place discussing the command tag, other than libpq's documentation. Is there one somewhere? While working on the docs, I noticed one problem with the patch itself: it doesn't handle multi-statement DO INSTEAD rules correctly. I'm going to submit a fix for that later. Any suggestions, whatsoever, are welcome. Regards, Marko Tiikkaja
*** a/doc/src/sgml/queries.sgml --- b/doc/src/sgml/queries.sgml *************** *** 1530,1538 **** SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression <para> <literal>WITH</> provides a way to write subqueries for use in a larger ! <literal>SELECT</> query. The subqueries 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 ( --- 1530,1538 ---- <para> <literal>WITH</> provides a way to write subqueries for use in a larger ! query. The subqueries 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 ( *************** *** 1560,1565 **** GROUP BY region, product; --- 1560,1586 ---- </para> <para> + <literal>WITH</> clauses are not restricted to only <literal>SELECT</> + queries; you can also use <literal>INSERT</>, <literal>UPDATE</> or + <literal>DELETE</>. This allows you to perform many different operations + in the same query. An example of this is: + + <programlisting> + WITH rows AS ( + DELETE FROM ONLY products + WHERE + "date" >= '2009-10-01' AND + "date" < '2009-11-01 + RETURNING * + ) + INSERT INTO products_log + SELECT * FROM rows; + </programlisting> + + which moves rows from products to products_log. + </para> + + <para> The optional <literal>RECURSIVE</> modifier changes <literal>WITH</> from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. Using *** a/doc/src/sgml/ref/create_rule.sgml --- b/doc/src/sgml/ref/create_rule.sgml *************** *** 222,227 **** CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS --- 222,234 ---- </para> <para> + In an <literal>INSERT</literal>, <literal>UPDATE</literal> or + <literal>DELETE</literal> query within a <literal>WITH</literal> clause, + only unconditional, single-statement <literal>INSTEAD</literal> rules are + implemented. + </para> + + <para> It is very important to take care to avoid circular rules. For example, though each of the following two rule definitions are accepted by <productname>PostgreSQL</productname>, the *** a/doc/src/sgml/ref/delete.sgml --- b/doc/src/sgml/ref/delete.sgml *************** *** 21,30 **** PostgreSQL documentation --- 21,36 ---- <refsynopsisdiv> <synopsis> + [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ] DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ] [ USING <replaceable class="PARAMETER">using_list</replaceable> ] [ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ] + + <phrase>where <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> ) + </synopsis> </refsynopsisdiv> *************** *** 84,89 **** DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] --- 90,104 ---- <variablelist> <varlistentry> + <term><replaceable class="PARAMETER">with_query</replaceable></term> + <listitem> + <para> + For information about with_query, see + <xref linkend="sql-with" endterm="sql-with-title">. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>ONLY</></term> <listitem> <para> *** a/doc/src/sgml/ref/insert.sgml --- b/doc/src/sgml/ref/insert.sgml *************** *** 21,29 **** PostgreSQL documentation --- 21,36 ---- <refsynopsisdiv> <synopsis> + [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ] INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ] { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> } [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ] + + <phrase>where <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> ) + + </synopsis> </refsynopsisdiv> *************** *** 85,90 **** INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable --- 92,106 ---- <variablelist> <varlistentry> + <term><replaceable class="PARAMETER">with_query</replaceable></term> + <listitem> + <para> + For information about with_query, see + <xref linkend="sql-with" endterm="sql-with-title">. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><replaceable class="PARAMETER">table</replaceable></term> <listitem> <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> *************** *** 202,209 **** 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> --- 202,209 ---- <para> The <literal>WITH</literal> clause allows you to specify one or more ! statements that can be referenced by name in the primary query. ! The output of those statements effectively act as temporary tables or views for the duration of the primary query. </para> *************** *** 211,229 **** TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | 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 ] <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> --- 211,261 ---- 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 statement. ! </para> ! ! <para> ! You can also use <literal>INSERT</literal>, <literal>UPDATE</literal> and ! <literal>DELETE</literal> in a <literal>WITH</literal> query. These statements ! are executed, in order, before the main query is executed and the results of ! their respective <literal>RETURNING</literal> clauses are made available to the ! main query. If a statement doesn't have a <literal>RETURNING</literal> clause, ! it is still executed normally, but referring to its <literal>WITH</literal> query ! will result in an error. You can mix <literal>SELECT</literal>, ! <literal>INSERT</literal>, <literal>UPDATE</literal> and <literal>DELETE</literal> ! statements in a single <literal>WITH</literal> list. Only <literal>SELECT</literal> ! queries are allowed below the top level. ! </para> ! ! <note> ! <para> ! Because <literal>INSERT</literal>, <literal>UPDATE</literal> and ! <literal>DELETE</literal> statements within <literal>WITH</literal> ! clauses need to be executed before the main query, their ! <literal>RETURNING</literal> results need to be stored somewhere. If those ! rows don't fit into <varname>work_mem</varname>, they will be stored on disk ! for the duration of the whole query. ! </para> ! </note> ! ! <para> ! In an <literal>INSERT</literal>, <literal>UPDATE</literal> or ! <literal>DELETE</literal> query within a <literal>WITH</literal> statement, ! only unconditional, single-statement <literal>INSTEAD</literal> rules are ! implemented. </para> <para> If <literal>RECURSIVE</literal> is specified, it allows a ! SELECT query to reference itself by name. Such a statement must have the form <synopsis> <replaceable class="parameter">non_recursive_term</replaceable> UNION [ ALL ] <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. INSERT, UPDATE and DELETE are not permitted in ! a recursive query. </para> <para> *************** *** 233,239 **** TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | circular references, or mutual recursion, are not implemented.) Without <literal>RECURSIVE</literal>, <literal>WITH</literal> queries can only reference sibling <literal>WITH</literal> queries ! that are earlier in the <literal>WITH</literal> list. </para> <para> --- 265,273 ---- circular references, or mutual recursion, are not implemented.) Without <literal>RECURSIVE</literal>, <literal>WITH</literal> queries can only reference sibling <literal>WITH</literal> queries ! that are earlier in the <literal>WITH</literal> list. For INSERT, ! UPDATE and DELETE the WITH queries are reordered to eliminate ! these forward references and executed in that order. </para> <para> *** a/doc/src/sgml/ref/update.sgml --- b/doc/src/sgml/ref/update.sgml *************** *** 21,32 **** PostgreSQL documentation --- 21,38 ---- <refsynopsisdiv> <synopsis> + [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ] UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ] SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } | ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...] [ FROM <replaceable class="PARAMETER">from_list</replaceable> ] [ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ] + + <phrase>where <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> ) + </synopsis> </refsynopsisdiv> *************** *** 80,85 **** UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep --- 86,100 ---- <variablelist> <varlistentry> + <term><replaceable class="PARAMETER">with_query</replaceable></term> + <listitem> + <para> + For information about with_query, see + <xref linkend="sql-with" endterm="sql-with-title">. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><replaceable class="PARAMETER">table</replaceable></term> <listitem> <para>
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers