Re: DB2-style INS/UPD/DEL RETURNING
От | Jonah H. Harris |
---|---|
Тема | Re: DB2-style INS/UPD/DEL RETURNING |
Дата | |
Msg-id | 36e682920603130752w4dc4d954leafc11426effe6b8@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: DB2-style INS/UPD/DEL RETURNING (Dave Cramer <pg@fastcrypt.com>) |
Ответы |
Re: DB2-style INS/UPD/DEL RETURNING
|
Список | pgsql-hackers |
On 3/13/06, Dave Cramer <pg@fastcrypt.com> wrote:
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
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
В списке pgsql-hackers по дате отправления: