Postgres Pro
Downloads
Home   >   mailing lists

Re: Returning generated id after a transaction. - Mailing list pgsql-novice

From Guillaume Henriot
Subject Re: Returning generated id after a transaction.
Date
Msg-id CALJcmg9mz4=k50poCfavYmML=tbVreAmOV8QGd33Sk=oVK1mkw@mail.gmail.com
Whole thread Raw
In response to Re: Returning generated id after a transaction.  (Guillaume Henriot <henriotg@gmail.com>)
Responses Re: Returning generated id after a transaction.  (Bartosz Dmytrak <bdmytrak@gmail.com>)
List pgsql-novice
Tree view
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 ?

Le 23 avril 2012 17:17, Guillaume Henriot <henriotg@gmail.com> a écrit :
Thank you so much for these examples, I tryed the 4th one for it's easiness and it works perfectly ! I'm probably going to replace it with your no.2 example as soon as I have time as I do agree with you about data and application logic.

Thanks again,
Guillaume

Le 21 avril 2012 10:26, Bartosz Dmytrak <bdmytrak@gmail.com> a écrit :

Hi,
there is option no. 4 (kind of extended WITH)

WITH 
inserted_row AS (
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING *
), 
updated_row AS (UPDATE "tblChild"
SET "ParentRowId" = (SELECT "RowId" FROM inserted_row)
WHERE "RowId" = 123 --whatever You need
)

SELECT "RowId" FROM inserted_row;

I missed it in previous post.
This could be part of function (SELECT statement should be modified) or ad-hoc query.

Regards,
Bartek




pgsql-novice by date:

Previous
From: Guillaume Henriot
Date:
Subject: Re: Returning generated id after a transaction.
Next
From: Jim Blizard
Date:
Subject: Re: logging pg_dump results in windows 7