Skip to Main Content

Support RETURNING also in the SQL language

Lukas EderMay 15 2018

The PL/SQL language has the useful RETURNING clause, which can be added to most DML statements:

DECLARE

   v_result tab.id%TYPE;

BEGIN

   INSERT INTO tab (col)

  VALUES (123)

  RETURNING id INTO v_result;

END;

/

It would be really useful if the SQL language also supported this clause, exactly like PostgreSQL and Firebird do as well:

INSERT INTO tab (col)

VALUES (123)

RETURNING id

This suggestion is one option to implement the desired feature. It would make the SQL and PL/SQL languages a bit more consistent. A much better option, of course, would be to implement the SQL standard (which is supported by DB2). In that case, the following syntax could be used:

SELECT *

FROM FINAL TABLE (

   INSERT INTO tab (col)

   VALUES (123)

)

That syntax is very powerful, as the results of several DML operations can be joined or otherwise combined (e.g. with unions). The clause is called <data change delta table> in the SQL:2016 standard, and is part of the Feature T495, “Combined data change and retrieval”

Comments
Post Details
Added on May 15 2018
6 comments
264 views