WriteableCTEs

Overview

Writeable CTEs add "(INSERT | UPDATE | DELETE) .. [RETURNING ..]" to CTEs(Common Table Expressions), both in the WITH clause and the top-level statement.

Syntax

   [WITH 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 )

Terminology

DML

An INSERT, DELETE, or UPDATE statement.

Derived table

A temporary table (set of tuples) returned by table expressions.

General Rules

• Top level DML WITH clauses are executed using a single snapshot. This query would remove all rows from "x" and return the number of rows in "x" before DELETE (i.e. the number of deleted rows).

   WITH t1 AS (DELETE FROM x)
      SELECT count(*) FROM x;

• DMLs are executed as separated plans.
• DMLs in WITH list without RECURSIVE clause can refer to only other table expressions that precedes to it as literally specified. This behavior is the same as normal SELECT CTEs. 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;

• AFTER triggers will not run until the whole query has been executed.
• You cannot CREATE VIEW on wCTEs query. This restriction may be removed in the future.
• It is allowed to put DMLs 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;

• DMLs in WITH clause are allowed only in the top-level WITH.

   -- raise an error
   SELECT * FROM(WITH t1 AS(DELETE FROM src) SELECT * FROM t1)s;

Implementation

[D] Allow the "main query" to be DMLs

• There were some problems with this for 9.0, see http://archives.postgresql.org/pgsql-hackers/2010-02/msg01065.php

Usage

• Move rows from src to dest, and return them immediately.

   WITH t1 AS (DELETE FROM src RETURNING *),
      INSERT INTO dest SELECT * FROM t1 RETURNING *;

Compatibility

Because RETURNING clause on DMLs is PostgreSQL's extension, writeable CTEs is not in the current standard. However, SQL 2011 possibly mentions about Delta Table, which might be similar to Writeable CTEs.