Skip navigation

RETURNING INTO - support both pre-update and post-update values

score 230
You have not voted. Active

The RETURNING INTO clause returns data from the rows that have been affected by the DML statement:

For INSERT it returns the after-insert values of the new row's columns.
For UPDATE it returns the after-update values of the affected rows' columns.
For DELETE it returns the before-delete values of the affected rows' columns.

For INSERT there are no before-insert values, so the "after-insert values" is the only reasonable option.

Likewise, for DELETE there are no after-delete values, so the "before-delete values" is the only reasonable option.

 

But for UPDATE there are both before-update and after-update values, but currently the RETURNING INTO clause supports only the after-update values.

 

I think it will be really useful if for UPDATE we'll be able to get the before-update values in addition to the after-update values. This will also make the feature more symmetric and complete, in my opinion.

Currently, if we need to know the before-update values, we have to make two SQL statement calls: SELECT (FOR UPDATE) for the record(s) we're about to update, and then the UPDATE itself.

 

For more details, and an example, please see http://db-oriented.com/2017/10/07/returning-into-enhancement-suggestion

 

Thank you,

Oren.

Comments

Vote history