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

Re: Returning generated id after a transaction.

From: Guillaume Henriot <henriotg(at)gmail(dot)com>
To: Bartosz Dmytrak <bdmytrak(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Returning generated id after a transaction.
Date: 2012-04-19 14:18:58
Message-ID: CALJcmg85fZP3wv9=WJeLeuAPf4GWv0Xq92P4ptF17x+CcPpo=A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Bartek,

I'm currently using version 9.1.3 on windows but it will be used on a
fedora server afterwards if it changes anything.
I'm not sure I know the difference between anonymous block and ad hoc
query, but it's just a block I wrote in an admin page for a php based
website. It works well without the BEGIN and COMMIT and gives me back the
id but as soon as I add the transaction part I can't retrieve it.

Should I try in a function or stored procedure, I thought i'd start with
the simplier version first to debug because it has a lot of parameters.

Thank you for your time,
Guillaume

Le 18 avril 2012 21:45, Bartosz Dmytrak <bdmytrak(at)gmail(dot)com> a écrit :

> Hi,
> which postgres version You are using?
> do You perform this in function or as anonymous block, or at hoc query?
>
> regards,
> Bartek
>
>
>
> 2012/4/18 Guillaume Henriot <henriotg(at)gmail(dot)com>
>
>> Hi,
>>
>> First of all excuse me if i'm not precise enough, english is not my
>> native language.
>>
>> I have a small problem while using transactions. I'm doing an INSERT in
>> one table, an UPDATE in another table and then i'm trying to retrieve the
>> id generated by the serial in the first table but the few things I have
>> tryed does not return anything. Here is a sample of my transaction:
>>
>> 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 have tryed with RETURNING id on the INSERT too and pgadmin tells me
>> each time this kind of message : "query result of 1 line cancelled".
>>
>> It works well when I do it without the begin and commit, so as a pgsql
>> beginner I'm a bit lost.
>>
>>
>> Guillaume Henriot
>>
>
>

In response to

Responses

pgsql-novice by date

Next:From: Ennio-SrDate: 2012-04-19 14:45:35
Subject: Re: Escaping literal strings in pg_8.4
Previous:From: ennioDate: 2012-04-18 22:16:54
Subject: Re: Escaping literal strings in pg_8.4

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