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