*** 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