Обсуждение: SQL2011 and writeable CTE
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. Regards, -- Hitoshi Harada
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. ;-)
2010/11/8 Peter Eisentraut <peter_e@gmx.net>: > 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. Hmm, this looks very different from our writeable CTEs. And I can see many issues like syntax ambiguity and execution order if we support it. AFAIK the most significant reason why we are working on CTEs is that CTEs are regarded as something like materialized table. So I think we are going on writeable "CTEs" unless there are no objection rather than pursuing the standard. Thanks for sharing anyway. -- Hitoshi Harada
2010/11/8 Hitoshi Harada <umi.tanuki@gmail.com>: > 2010/11/8 Peter Eisentraut <peter_e@gmx.net>: >> 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. > > Hmm, this looks very different from our writeable CTEs. And I can see > many issues like syntax ambiguity and execution order if we support > it. AFAIK the most significant reason why we are working on CTEs is > that CTEs are regarded as something like materialized table. > > So I think we are going on writeable "CTEs" unless there are no > objection rather than pursuing the standard. > Thanks for sharing anyway. > I found, so writeable CTE was implemented in Microsoft SQL server too. Can be our implementation compatible? Regards Pavel > > > -- > Hitoshi Harada > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
2010/11/8 Pavel Stehule <pavel.stehule@gmail.com>: > 2010/11/8 Hitoshi Harada <umi.tanuki@gmail.com>: >> Hmm, this looks very different from our writeable CTEs. And I can see >> many issues like syntax ambiguity and execution order if we support >> it. AFAIK the most significant reason why we are working on CTEs is >> that CTEs are regarded as something like materialized table. >> >> So I think we are going on writeable "CTEs" unless there are no >> objection rather than pursuing the standard. >> Thanks for sharing anyway. >> > > I found, so writeable CTE was implemented in Microsoft SQL server too. > Can be our implementation compatible? > I don't believe MS SQL Server doesn't implement such our writeable CTEs. If you mention about this link (http://msdn.microsoft.com/en-us/library/ms175972.aspx), it says: [ WITH <common_table_expression> [ ,...n ] ] <common_table_expression>::= expression_name [ (column_name [ ,...n ] ) ] AS (CTE_query_definition) and CTE_query_definition Specifies a SELECT statement whose result set populates the common table expression. <snip> It says about top-level DML under CTE shown below which we already have in our HEAD. WITH t AS (SELECT * FROM src) INSERT INTO dest SELECT * FROM t; as "A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view." They are different. Regards, -- Hitoshi Harada
2010/11/9 Hitoshi Harada <umi.tanuki@gmail.com>: > 2010/11/8 Pavel Stehule <pavel.stehule@gmail.com>: >> 2010/11/8 Hitoshi Harada <umi.tanuki@gmail.com>: > >>> Hmm, this looks very different from our writeable CTEs. And I can see >>> many issues like syntax ambiguity and execution order if we support >>> it. AFAIK the most significant reason why we are working on CTEs is >>> that CTEs are regarded as something like materialized table. >>> >>> So I think we are going on writeable "CTEs" unless there are no >>> objection rather than pursuing the standard. >>> Thanks for sharing anyway. >>> >> >> I found, so writeable CTE was implemented in Microsoft SQL server too. >> Can be our implementation compatible? >> > > I don't believe MS SQL Server doesn't implement such our writeable I mean, "I don't believe it implements..." :) -- Hitoshi Harada
2010/11/8 Hitoshi Harada <umi.tanuki@gmail.com>: > 2010/11/9 Hitoshi Harada <umi.tanuki@gmail.com>: >> 2010/11/8 Pavel Stehule <pavel.stehule@gmail.com>: >>> 2010/11/8 Hitoshi Harada <umi.tanuki@gmail.com>: >> >>>> Hmm, this looks very different from our writeable CTEs. And I can see >>>> many issues like syntax ambiguity and execution order if we support >>>> it. AFAIK the most significant reason why we are working on CTEs is >>>> that CTEs are regarded as something like materialized table. >>>> >>>> So I think we are going on writeable "CTEs" unless there are no >>>> objection rather than pursuing the standard. >>>> Thanks for sharing anyway. >>>> >>> >>> I found, so writeable CTE was implemented in Microsoft SQL server too. >>> Can be our implementation compatible? >>> >> >> I don't believe MS SQL Server doesn't implement such our writeable > > I mean, "I don't believe it implements..." :) ook :) Pavel > > -- > Hitoshi Harada >
On mån, 2010-11-08 at 10:34 +0900, Hitoshi Harada wrote: > Hmm, this looks very different from our writeable CTEs. And I can see > many issues like syntax ambiguity and execution order if we support > it. AFAIK the most significant reason why we are working on CTEs is > that CTEs are regarded as something like materialized table. > > So I think we are going on writeable "CTEs" unless there are no > objection rather than pursuing the standard. Well, I have not followed the "writable CTE" ongoings closely, but it seems to me that a) It is not very different at all. b) Issues such as syntax ambiguity and execution order surely have a resolution somewhere if you dig a bit deeper. c) You will run into these issues anyway, even you use a different syntax. d) There are more issues lurking that you haven't thought of yet but the SQL standards writers might have.