Обсуждение: Help

Hi ,

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.

Thanks & Regards
Ritanjali Majhee

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.



--
john r pierce, recycling bits in santa cruz

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.