*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 1530,1538 **** SELECT select_list FROM table_expression
WITH> provides a way to write subqueries for use in a larger
! 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:
WITH regional_sales AS (
--- 1530,1538 ----
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:
WITH regional_sales AS (
***************
*** 1560,1565 **** GROUP BY region, product;
--- 1560,1586 ----
+ WITH> clauses are not restricted to only SELECT>
+ queries; you can also use INSERT>, UPDATE> or
+ DELETE>. This allows you to perform many different operations
+ in the same query. An example of this is:
+
+
+ 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;
+
+
+ which moves rows from products to products_log.
+
+
+
The optional RECURSIVE> modifier changes 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 name AS
--- 222,234 ----
+ In an INSERT, UPDATE or
+ DELETE query within a WITH clause,
+ only unconditional, single-statement INSTEAD rules are
+ implemented.
+
+
+
It is very important to take care to avoid circular rules. For
example, though each of the following two rule definitions are
accepted by PostgreSQL, the
*** a/doc/src/sgml/ref/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***************
*** 21,30 **** PostgreSQL documentation
--- 21,36 ----
+ [ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
+
+ where with_query is:
+
+ with_query_name [ ( column_name [, ...] ) ] AS ( select | insert | update | delete )
+
***************
*** 84,89 **** DELETE FROM [ ONLY ] table [ [ AS ]
--- 90,104 ----
+ with_query
+
+
+ For information about with_query, see
+ .
+
+
+
+
ONLY>
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 21,29 **** PostgreSQL documentation
--- 21,36 ----
+ [ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
+
+ where with_query is:
+
+ with_query_name [ ( column_name [, ...] ) ] AS ( select | insert | update | delete )
+
+
***************
*** 85,90 **** INSERT INTO table [ (
+ with_query
+
+
+ For information about with_query, see
+ .
+
+
+
+
table
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 58,64 **** SELECT [ ALL | DISTINCT [ ON ( expressionand with_query is:
! with_query_name [ ( column_name [, ...] ) ] AS ( select )
TABLE { [ ONLY ] table_name [ * ] | with_query_name }
--- 58,64 ----
and with_query is:
! with_query_name [ ( column_name [, ...] ) ] AS ( select | insert | update | delete )
TABLE { [ ONLY ] table_name [ * ] | with_query_name }
***************
*** 202,209 **** TABLE { [ ONLY ] table_name [ * ] |
The WITH 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.
--- 202,209 ----
The WITH 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.
***************
*** 211,229 **** TABLE { [ ONLY ] table_name [ * ] |
A name (without schema qualification) must be specified for each
WITH query. Optionally, a list of column names
can be specified; if this is omitted,
! the column names are inferred from the subquery.
If RECURSIVE is specified, it allows a
! subquery to reference itself by name. Such a subquery must have
the form
non_recursive_term UNION [ ALL ] recursive_term
where the recursive self-reference must appear on the right-hand
side of the UNION>. Only one recursive self-reference
! is permitted per query.
--- 211,261 ----
A name (without schema qualification) must be specified for each
WITH query. Optionally, a list of column names
can be specified; if this is omitted,
! the column names are inferred from the statement.
!
!
!
! You can also use INSERT, UPDATE and
! DELETE in a WITH query. These statements
! are executed, in order, before the main query is executed and the results of
! their respective RETURNING clauses are made available to the
! main query. If a statement doesn't have a RETURNING clause,
! it is still executed normally, but referring to its WITH query
! will result in an error. You can mix SELECT,
! INSERT, UPDATE and DELETE
! statements in a single WITH list. Only SELECT
! queries are allowed below the top level.
!
!
!
!
! Because INSERT, UPDATE and
! DELETE statements within WITH
! clauses need to be executed before the main query, their
! RETURNING results need to be stored somewhere. If those
! rows don't fit into work_mem, they will be stored on disk
! for the duration of the whole query.
!
!
!
!
! In an INSERT, UPDATE or
! DELETE query within a WITH statement,
! only unconditional, single-statement INSTEAD rules are
! implemented.
If RECURSIVE is specified, it allows a
! SELECT query to reference itself by name. Such a statement must have
the form
non_recursive_term UNION [ ALL ] recursive_term
where the recursive self-reference must appear on the right-hand
side of the UNION>. Only one recursive self-reference
! is permitted per query. INSERT, UPDATE and DELETE are not permitted in
! a recursive query.
***************
*** 233,239 **** TABLE { [ ONLY ] table_name [ * ] |
circular references, or mutual recursion, are not implemented.)
Without RECURSIVE, WITH queries
can only reference sibling WITH queries
! that are earlier in the WITH list.
--- 265,273 ----
circular references, or mutual recursion, are not implemented.)
Without RECURSIVE, WITH queries
can only reference sibling WITH queries
! that are earlier in the WITH list. For INSERT,
! UPDATE and DELETE the WITH queries are reordered to eliminate
! these forward references and executed in that order.
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 21,32 **** PostgreSQL documentation
--- 21,38 ----
+ [ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
SET { column = { expression | DEFAULT } |
( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
+
+ where with_query is:
+
+ with_query_name [ ( column_name [, ...] ) ] AS ( select | insert | update | delete )
+
***************
*** 80,85 **** UPDATE [ ONLY ] table [ [ AS ]
+ with_query
+
+
+ For information about with_query, see
+ .
+
+
+
+
table