Re: Returning generated id after a transaction.

Ah yes, I did not see I left a mistake when I renamed my variables for
readability in my example. It should be 'table1_id_seq' instead of
'parts_id_seq' and it gives me the same exception you had. I understand you
don't need BEGIN/COMMIT in pgadmin, but I was just trying to test this
block as it did not send anything back if I pasted it in my php code too.

Anyway I was just curious, it's not that important, I'll start using your
examples and everything should be better :)

Guillaume

Le 24 avril 2012 20:46, Bartosz Dmytrak <bdmytrak(at)gmail(dot)com> a écrit :

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