Re: DB2-style INS/UPD/DEL RETURNING

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> On Sun, 12 Mar 2006, Jonah H. Harris wrote:
>> SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)

> This doesn't solve the generated keys problem that the Java and probably
> .NET interfaces have. Mind, RETURNING doesn't solve anything either.

Why not? AFAICS, either one lets you get at generated keys.

It's quite unclear to me what the difference is between "FINAL" and
"NEW" ... any clarification there?

The "OLD" idea is cute but I'm not sure how useful it really is. They
seem to have missed a bet anyway: if I understand how this works, you
can't get values from both new and old row states in the UPDATE case.
The classification seems bogus for both INSERT and DELETE, too; neither
of them have more than one row state to deal with.

Also, is the front SELECT allowed to have its own WHERE, or is it
constrained to return exactly one row per inserted/updated/deleted row?
If it can have a WHERE then there's a syntactic ambiguity in
SELECT ... FROM NEW TABLE UPDATE ... WHERE ...

More generally, this syntax is problematic in that it's syntactically
possible to use SELECT FROM NEW TABLE ... as a sub-query, which seems
like a truly horrid idea from both semantics and implementation
perspectives.

regards, tom lane