[
https://issues.apache.org/jira/browse/DERBY-6953?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16097472#comment-16097472
]
Rick Hillegas commented on DERBY-6953:
--------------------------------------
Thanks for that reference, Lukas. The grammar stanzas you cited are from the
2016 Standard, part 2, section 7.6 <table reference>. According to GR 5b, for
an INSERT, with <result option> NEW, the contents of the <data change delta
table> are defined by section 15.10 (Effect of inserting tables into base
tables). It appears to be the set of all inserted rows, including all of their
columns. My reading of this language is that the user would phrase an insert
statement like this:
{noformat}
select keyCol from new table
(
insert into t(a, b, c) select (x, y, z) from s
)
;
{noformat}
See, for instance,
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/apsg/src/tpc/db2z_selectvaluesmerge.html
I think that there is a fair amount of language to build here in order to wire
this into Derby's access control language and trigger mechanism. However, the
solution is attractive. For best performance, we would want to push the
projection (keyCol in the example above) down into the <data change delta
table> so that the engine only collects the desired column (keyCol) as the
engine processes the insert.
Thanks,
-Rick
> Support Standard <data change delta table> syntax for retrieving INSERTed key
> values
> ------------------------------------------------------------------------------------
>
> Key: DERBY-6953
> URL: https://issues.apache.org/jira/browse/DERBY-6953
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.13.1.1
> Reporter: Lukas Eder
>
> The SQL standard supports an interesting syntax that can be used as a <table
> reference>:
> {code}
> <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
> {code}
> This is currently supported by DB2. Databases like Firebird, Oracle (in
> PL/SQL), PostgreSQL support an alternative syntax through the RETURNING
> keyword that can be appended to <data change statement>. SQL Server has an
> OUTPUT keyword that can be placed in the middle of a <data change statement>.
> These statements are incredibly useful to retrieve generated ID values but
> also trigger-generated values after a DML operation for an arbitrary number
> of inserted / updated / deleted / merged rows.
> It would allow people to bypass the many problems that are currently still
> open related to Statement.getGeneratedKeys(). Quite likely, if these clauses
> were made available, Statement.getGeneratedKeys() could be implemented by
> patching the user-defined SQL to be wrapped with a <data change delta table>
> clause.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)