From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Need magic for inserting in 2 tables
Date: Mon, 04 Oct 2010 02:47:26 +0200
Message-ID: <4CA9241E.9030709@gmx.net> (raw)
In-Reply-To: <AANLkTinibE8HA3nipALO_aGsgxYDACf0xokUiPJrq5bz@mail.gmail.com>
References: <4CA9004E.7060106@gmx.net>
<AANLkTinibE8HA3nipALO_aGsgxYDACf0xokUiPJrq5bz@mail.gmail.com>
Am 04.10.2010 01:46, schrieb Scott Marlowe:
> On Sun, Oct 3, 2010 at 4:14 PM, Andreas<maps.on@gmx.net> wrote:
>> insert into staff ( company_fk, ..., department_fk )
>> select company_fk, ..., department_fk
>> from departments, companies, company_2_project AS c2p
>> where company_id = c2p.company_fk
>> and c2p.project_fk = 42
>> and department_id in ( 40, 50 );
>>
>> step 2 would be to link those new blank staff records to project 42 by
>> inserting a record into staff_2_project for every new staff_id.
>>
>> How can I find the new staff_ids while making sure I don't insert ids from
>> other sessions?
>> Is there an elegant way in SQL ?
> Use returning?
>
> insert into .....
> yada
> returning field1, field2, field3
It seams the inserts can't be chained? :(
The inner insert works when I run it separately but when I run the
chained inserts I get an syntax error.
How can a script use what RETURNING dumps out?
I tried a bit but got nowhere.
insert into staff_2_project ( staff_fk, project_fk )
insert into staff ( company_fk, ..., department_fk )
[...]
returning staff_id, 42 as project_fk;
and
insert into staff_2_project ( staff_fk, project_fk )
(
insert into staff ( company_fk, ..., department_fk )
[...]
returning staff_id, 42 as project_fk
) as s;
and
insert into staff_2_project ( staff_fk, project_fk )
select staff_id, project_fk from
(
insert into staff ( company_fk, ..., department_fk )
[...]
returning staff_id, 42 as project_fk
) as s;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net
Subject: Re: Need magic for inserting in 2 tables
In-Reply-To: <4CA9241E.9030709@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox