Bio

David Fetter is based in the San Francisco Bay Area and has worked in various commercial enterprises, non-profits and educational institutions. He has worked extensively with PostgreSQL, Oracle, MySQL, Perl, PHP, PL/SQL, PL/PgSQL, PL/Perl and (of course!) vim on transaction processing and business intelligence systems.

In his free time, he brews beer, rides his bicycle, and helps run several organizations for computer professionals including the Oakland Perl Users' Group, the San Francisco PostgreSQL Users' Group and the San Francisco Perl Users' Group.

Writeable CTEs: The Next Big Thing

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.

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) ...