Hi all,
I am working on a project which requires INSERT/UPDATE ... RETURNING
functionality. As far as I can tell, Oracle is one of the only commercial
databases supporting this functionality. I checked DB2, Informix 7.2
(which is the most recent release for which I have a manual), Sybase and
Interbase. I also checked Firebird and MySQL.
Oracle returns data into binded variables, which we do not support. It
returns multiple rows into bind arrays, ie, an array of instances of
binded variables.
Why is RETURNING useful?
1) It simplifies the use of sequences
2) Situations where applications don't know about the data which is being
stored. Eg:
i) INSERT .. VALUES(DEFAULT,...)
ii) UPDATE ... set col=col+X
and variations there of.
Issues with implementing this under Postgres:
1) Returning multiple rows
I see no read why multiple rows should not be returned if multiple rows
are affected. I cannot see any technical reason why this should not be the
case.
2) Same row affected multiple times
UPDATEs can affect rows multiple times. It would be very easy to just
return every tuple affected but that seems broken to me. The logical
solution is to return that tuple which eventually results from the
UPDATE. Unfortunately, that means that we cannot just printtup(). It'll
add a bit of overhead to traverse already UPDATEd tuples for every tuple
-- particularly if we hit disk. Ideas?
3) Inherited updates affecting multiple tables
Seems that it would be the right thing to do to allow returning of
inherited rows, but it might be a bit painful to implement. Ideas?
4) Handling rule cases
Seems reasonable to allow RETURNING when the query is re-written to a
single query and that the operation is not transformed.
5) Permissions
To use RETURNING, the user must have select privileges on the
table(s) being affected
6) Protocol changes
I don't see that this will affect the FE/BE protocol.
7) Zero affected rows
Return zero rows.
Comments, ideas?
Thanks,
Gavin
