From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com> |
Cc: | Ritanjali M <ritanjalim60(at)gmail(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: Help |
Date: | 2016-03-18 01:11:39 |
Message-ID: | CAKFQuwZc++9hy1PdxTdonZyi3AAGBQrECKd8yTA+xg0rvbKqZg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Thursday, March 17, 2016, John R Pierce <pierce(at)hogranch(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | cjthmp | 2016-03-18 03:50:10 | BUG #14031: Failed to Write to History File |
Previous Message | David G. Johnston | 2016-03-18 01:02:42 | Re: BUG #14025: Unable to validate constraints |