Skip site navigation (1) Skip section navigation (2)

Re: Returning generated id after a transaction.

From: Bartosz Dmytrak <bdmytrak(at)gmail(dot)com>
To: Guillaume Henriot <henriotg(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Returning generated id after a transaction.
Date: 2012-04-24 18:46:52
Message-ID: CAD8_UcY1nN=O-j9NMq1nJhFDAG5Bo6BHAUTRLig4K7LYTwWZdw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Willy-Bas LoosDate: 2012-04-25 11:30:18
Subject: Re: Using Table Indexes After Joins
Previous:From: Jim BlizardDate: 2012-04-23 17:46:37
Subject: Re: logging pg_dump results in windows 7

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group