Re: DB2-style INS/UPD/DEL RETURNING

On Mon, 13 Mar 2006, Tom Lane wrote:

> 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.

There are a few different ways to get at generated keys from JDBC at
least. The case we cannot trivially deal with is when the code executes a
statement and then wants a result set of all generated keys. That is, it
doesn't register which generated keys it wants returned before the query
is executed.

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

NEW returns the representation of the data which the statement creates;
FINAL is the final representation of the data, after AFTER triggers have
been applied.

>
> 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.

Right, it's not as useful as our OLD.*, NEW.*.

>
> 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 ...

That's definately ambiguous. The manual doesn't clarify and I do not have
DB2 installed locally.

>
> 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.

I cannot see any reference to whether this is allowed in DB2. The DB2
manual and other IBM apps use it extensively in named expressions. Ie,

WITH
foo as (SELECT FROM NEW TABLE(...)),
bar as (SELECT FROM OLD TABLE(...))
SELECT ... FROM foo, bar

It does say that a 'data change table reference' is simply a type of table
reference so I suppose it can occur in a sub query. The ability to have
INSERT ... RETURNING in a from clause would achieve most of this, I think.

Thanks,

Gavin