dynamobi/dynamobi — Issue #417: [FRG-382] Support RETURNING clause for INSERT, UPDATE, MERGE, DELETE
Author: dynamobi-build
Created: 2011-12-30 03:13:05+00:00
State: open
Labels: FRG, Statement Processing

[reporter="jhyde", created="Tue, 6 Oct 2009 18:46:04 -0500 (GMT-05:00)"]
I just noticed that Postgres and Oracle support returning values from

INSERT and UPDATE statements using the new RETURNING clause. This

corrects a very long-standing issue with auto-increment columns, which

are currently very difficult to use efficiently when populating slowly

changing dimensions.

Here are some pointers to it:

* Postgres: http://www.postgresql.org/docs/8.2/static/sql-insert.html

* Oracle: http://lorentzcenter.nl/awcourse/oracle/server.920/a96540/statements_913a.htm

Not part of SQL:2008, but it would be nice to have it in LucidDB. Maybe also for the MERGE command. Anyone else agree?

----------

> Francisco Reyes replied:

>

> As a user, who won't have to be involved with the complexities of

> implementing the feature... I agree with it.

>

> I think it most usefull when doing one row, but it would be up to the

> calling program/script/person to determine how to use the return values.

>

> Also, on the docs we should mention it is a non standard extension.

-------------

> John Sichi replied:

>

> Yes, it would be useful. Oracle and PostgreSQL also support it for

> other DML statements such as DELETE:

>

> http://www.dba-oracle.com/t_delete_statement.htm

>

> Execution plans for DML statements are already super-complicated due to

> index updates and row rejection, but we could probably figure out a way

> to tee the results off to a temp area before the main storage updates

> happen, and then pull them back out as a coda to the final node which

> currently produces the rowcounts.