Re: SQL2011 and writeable CTE

On sön, 2010-11-07 at 21:28 +0900, Hitoshi Harada wrote:
> I've read this Peter's post:
> 
> http://petereisentraut.blogspot.com/2010/04/news-from-sql-standard.html
> 
> which says in SQL2011 has a new feature:
> 
> > Combined data change and retrieval. PostgreSQL does something like this with RETURNING, but this feature is more
elaborateand allows the writing of "delta tables".
 
> 
> And I heard from sources that this "delta table" contains something
> like writeable CTEs. Couldn't you share something about it if
> possible? I just don't like to design something that will be similar
> to but different from the standard.

Yeah, actually, after further study, this has nothing to do with actual
delta tables, and it is in fact exactly the RETURNING feature in
combination with the possibility to embed it into a superordinate query.

<table primary> ::= ... | <data change delta table> [ [ AS ] <correlation name> [ <left paren> <derived column list>
<rightparen> ] ]
 

<data change delta table> ::= <result option> TABLE <left paren> <data change statement> <right paren>

<data change statement> ::=   <delete statement: searched> | <insert statement> | <merge statement> | <update
statement:searched>
 

<result option> ::=   FINAL | NEW | OLD

So for example, instead of, say,

WITH foo AS (UPDATE ... RETURNING ...)  SELECT * FROM foo;

you would have

SELECT * FROM NEW TABLE (UPDATE ...);

In the case of an UPDATE statement, the NEW and OLD options allow you to
get the new or old version of the updated rows.  FINAL is like NEW but
it has some additional restrictions that relate to how it interacts with
foreign key referential actions.  It's too late for me now to figure out
the details of that. ;-)