On Thursday, March 17, 2016, John R Pierce <pierce@hogranch.com> wrote:
> On 3/17/2016 6:48 AM, Ritanjali M wrote:
>
>>
>> I am new to PostgreSQL ,i want to insert record into multiple table in a
>> single transaction and i need to get identity value from first table and
>> need to insert into second table with same transaction,so please help me
>> out with example.
>>
>>
>
> this is not a bug, and shouldn't be sent to the pgsql_bugs reporting mail
> list.
>
> you probably want to subscribe to and post to the pgsql-general email list
> for these sorts of questions....
>
> but, you would do something like..
>
> begin;
> insert into firsttable ..... returning id;
> insert into secondtable .... values (....including 'id' value
> returned by previous query...);
> commit;
>
> its up to your application program making these calls to accept the return
> value of the first insert and include it in the 2nd insert, the specifics
> of doing that are programming language and sql binding specific.
>
>
>
Or you can create a DO block and use pl/pgsql. Or, I think, common table
expressions (CTE/WITH)
DO $$
DECLARE result1_id bigint;
BEGIN
Roughly what John wrote
END;
$$
With insert_one AS (
Insert returning id
), insert_two AS (
insert into tbl2 (fk1)
Select id from insert_one
Returning
)
Select * from insert2;
You can turn the DO block into a formal function too.
Or just do the passing in the client like John said.
The CTE method I've never used and has limitations - a function or DO is
the most efficient method - the choice depends on your specifics.
David J.