PGCon2011 - Final (2011.06.11)

PGCon 2011
The PostgreSQL Conference

Speakers
David Fetter
Schedule
Day Talks - 2 - 2011-05-20
Room DMS 1120
Start time 11:30
Duration 01:00
Info
ID 378
Event type Lecture
Track 9.1 Features
Language used for presentation English
Feedback

Writeable CTEs

A Revolution in SQL

In SQL, Common Table Expressions are a way of creating and using views at run time, but only in read queries. Here we discover ways to use them in writes.

Find out about this exciting new feature!

We can make Common Table Expressions even more powerful than the current SQL standard allows. We might even make it part of a future SQL standard.

Old syntax:

WITH [RECURSIVE] cte1 [(col[, col...])] AS (
    READ_QUERY
[(UNION|INTERSECT) [ALL]
    READ_QUERY
)[, cte2 ...]
SELECT ...

New syntax:

WITH [RECURSIVE] cte1 [(col[, col...])] AS (
    ROW_RETURNING_QUERY /* for example, DELETE...RETURNING */
[(UNION|INTERSECT) [ALL]
    ROW_RETURNING QUERY
)[, cte2 ...]
(SELECT|INSERT|UPDATE|DELETE) ...