Re: Need magic for inserting in 2 tables

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;