PGCon2011 - Add 4 Video (2015.09.18)
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 |
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) ...