Re: Returning generated id after a transaction.

2012/4/23 Guillaume Henriot <henriotg(at)gmail(dot)com>

> Just one quick question again about my first problem, is that a limitation
> that code between begin and commit can't send back the id, or was it just a
> problem about my code ?
>

I think there is a bug in Your code:
BEGIN;
INSERT INTO table1 (id, name) VALUES (DEFAULT, 'name_of_the_entry');
UPDATE table2 SET table1_id = CURRVAL('table1_id_seq') WHERE id =
'some_row_id';
SELECT CURRVAL('parts_id_seq') AS table1_id;
COMMIT;

I looks like You are trying to select current value of other sequence. You
are trying to use table1_id_seq for update and parts_id_seq for select.

I tried similar code:
BEGIN;
INSERT INTO "tblParent" ("RowValue") VALUES ('2012-01-02'::date);
UPDATE "tblChild"
SET "ParentRowId" = currval('"tblParent_RowId_seq"'::regclass)
WHERE "RowId" = 1923;

SELECT currval('"tblParent_RowId_seq"'::regclass);
COMMIT;

works for me, but select doesn't produce any output in pgAdmin, only a
message:
*Query result with 1 row discarded. - *is that Your exception?
*Query returned successfully with no result in 26 ms. *

AFAIK pgAdmin runs all statements in SQL window in one transaction, so
there is no need to put everything in BEGIN...COMMIT

I have tried this code line by line i psql and works fine too:
myDatabase=# BEGIN;
BEGIN
myDatabase=# INSERT INTO "tblParent" ("RowValue") VALUES
('2012-01-02'::date);
INSERT 0 1
myDatabase=# UPDATE "tblChild"
SET "ParentRowId" = currval('"tblParent_RowId_seq"'::regclass)
WHERE "RowId" = 1923;
UPDATE 1
myDatabase=# SELECT currval('"tblParent_RowId_seq"'::regclass);
currval
---------
118
(1 row)

myDatabase=# COMMIT;
COMMIT

Regards,
Bartek