Re: DB2-style INS/UPD/DEL RETURNING

On 3/13/06, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
> One of the purposes of this as I understand it is to allow clients to
> get back the generated key(s). I don't see enough of the syntax to
> see if this is possible with the DB2 syntax below.

I believe it would be something like

CREATE SEQUENCE test_id_seq;
CREATE TABLE test_tbl (test_id bigint not null default
nextval('test_id_seq'), test_name varchar(32), PRIMARY KEY(test_id));

To get the generated sequence:
SELECT test_id FROM NEW TABLE INSERT INTO test_tbl VALUES
(nextval('test_id_seq'), 'Joe Blow');
The reason for NEW is because there is no OLD version of the record.

In contrast, something similar to:
SELECT test_name FROM OLD TABLE UPDATE test_tbl SET test_name = 'John Doe'
WHERE test_id = 1;
would return "Joe Blow"

Whereas:
SELECT test_name FROM NEW TABLE UPDATE test_tbl SET test_name = 'John Doe'
WHERE test_id = 1;
would return "John Doe"

Again, I haven't really used it, but have read over the docs briefly. I'm
just wondering if anyone has used it and likes/dislikes it.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324