From PostgreSQL wiki
Writeable CTEs add "(INSERT | UPDATE | DELETE) .. [RETURNING ..]" to CTEs(Common Table Expressions), both in the WITH clause and the top-level statement.
[WITH [ RECURSIVE ] with_query [, ...] plannable_statement
where with_query is:
with_query_name [ ( column_name [, ...] ) ] AS ( plannable_statement )
and plannable_statement (see gram.y for the origin of this term) query is:
( select | insert | update | delete )
- An INSERT, DELETE, or UPDATE statement.
- Derived table
- A temporary table (set of tuples) returned by table expressions.
- IDUs are not allowed in a truly recursive table expression, but you can mix recursive queries and IDUs in a single WITH list.
- IDUs are executed as the query says, whereas SELECTs may be omitted or its order of execution may be changed for optimization. (How?? may need to specify more precisely...)
- IDUs are executed exactly once, one by one, before starting to execute the top level query.
- IDUs are executed as separated plans.
- IDUs in WITH clause can refer to only other table expressions that precedes to it as literally specified. For example:
-- this should work WITH t1 AS(DELETE FROM src RETURNING *), t2 AS(INSERT INTO dest SELECT * FROM t1 RETURNING *) SELECT * FROM t2; -- this should raise error WITH t2 AS(INSERT INTO dest SELECT * FROM t1 RETURNING *), t1 AS(DELETE FROM src RETURNING *) SELECT * FROM t2;
- Triggers should work in each statement as it does for individual top level statement.
- You cannot CREATE VIEW on wCTEs query.
- It is allowed to put IDUs without RETURING clause in WITH, but it is forbidden to refer to that derived table.
-- this is ok WITH t1 AS(DELETE FROM src), t2 AS(SELECT * FROM dest) SELECT * FROM t2; -- this raises an error WITH t1 AS(DELETE FROM src), t2 AS(SELECT * FROM dest) SELECT * FROM t1;
Since this patch is quite complicated, I've split it up to a few milestones:
- [D] Add a new executor node, Derived Table scan, to support scanning from tuplestores.
- None of the existing executor nodes did only this (all of them deal with a subplan of some kind), so adding a new one made seemed to make sense.
- In the future, this node can be used to support other queries in WITHs (COPY, for example).
- Make all WITH queries work like wCTEs would
- What this means is that we run all WITH queries to completion and store the results in tuplestores. Then we proceed to execute the main plan.
- This means changes to the portal code, EXPLAIN ANALYZE, SQL function and SPI execution code.
- There's a proof-of-concept code in the git repo that works, but it's still very messy and only changes the portal code. It also doesn't handle snapshots correctly, but it's not yet clear what the code should look like (see NOTE near the bottom of this item). Not all regression tests for WITH are applicable because we don't need to support non-top-level CTEs, so they've been taken out from the git repo. Also a RECURSIVE query must not loop forever, so those tests are gone too. All remaining tests are passed now.
NOTE: There's an unresolved issue that affects this part of the plan, see http://archives.postgresql.org/message-id/4C49E821.email@example.com
- Allow IDUs inside top-level CTEs
- I think this stage can be completed by just copy-pasting from the patch considered for 9.0.
- Refactor executors for normal execution, EXPLAIN ANALYZE, SQL language functions and SPI to:
- save the results of a certain PlannedStmt
- maintain a list of those results
- [D] Allow the "main query" to be IDUs
- There were some problems with this for 9.0, see http://archives.postgresql.org/pgsql-hackers/2010-02/msg01065.php
These are just possibilities. They are not a roadmap. Your measurements may vary.
These are only memoranda or references...
- The execution order of each DML should be syntactically? DML -> SELECT?:
- pg_plan_query() returns List of PlannedStmt instead of one PlannedStmt:
- This should work:
WITH t1 AS (DELETE FROM src RETURNING *), t2 AS (INSERT INTO dest SELECT * FROM t1 RETURNING *) SELECT * FROM t2
- RECURSIVE query is fine as long as 1) it's SELECT-only and 2) it doesn't loop forever. WITH RETURNING cannot be contained in RECURSIVE. A wCTE can of course refer to the result of the recursive SELECT query with INSERT .. SELECT, UPDATE .. FROM or DELETE .. USING. :