Re: Returning generated id after a transaction.

2012/4/23 Guillaume Henriot <henriotg@gmail.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