Need magic for inserting in 2 tables

Hi,

I need to insert a lot of basically blank records into a table to be
filled later.
Sounds silly but please bear with me. :)

projects ( project_id, project_name, ... )
companies ( company_id, ... )
departments ( department_id, department )
staff ( staff_id SERIAL, company_fk, department_fk, ... )

company_2_project ( project_fk, company_fk )
staff_2_project ( project_fk, staff_fk, project data, ... )

So with this I can store that company 99 belongs e.g. to project 3, 5 and 42
and staff_id 11, 13, 17 belongs to company 99.

staff_2_project represents the connection of staff members to a project
and holds projectrelated infos.

Now say I have allready 100 companies out of the bigger adress pool
connected to project 42 and I now want to add blank staffers out of
department 40 and 50 linked with this project.

I do step 1:

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 ?

On Sun, Oct 3, 2010 at 4:14 PM, Andreas <maps(dot)on(at)gmx(dot)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

-- To understand recursion, one must first understand recursion.

Am 04.10.2010 01:46, schrieb Scott Marlowe:
> On Sun, Oct 3, 2010 at 4:14 PM, Andreas<maps(dot)on(at)gmx(dot)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;

On Sun, Oct 3, 2010 at 6:47 PM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:

> How can a script use what RETURNING dumps out?
> I tried a bit but got nowhere.

The same way it would use the output of a select, it's a record set.
So it's x rows by y columns.

--
To understand recursion, one must first understand recursion.

Am 04.10.2010 02:58, schrieb Scott Marlowe:
>
> The same way it would use the output of a select, it's a record set.
> So it's x rows by y columns.

Then where were my insert statements wrong?
Please, look this is a simple but complete example and show me my error.

create temporary table table_1 ( id_1 serial primary key, txt text );
create temporary table table_2 as select 42::integer as id_2;
ALTER TABLE table_2 ADD CONSTRAINT t2_pkey PRIMARY KEY( id_2 );
create temporary table t1_t2 ( fk_1 integer references table_1 ( id_1 ),
fk_2 integer references table_2 ( id_2 ) );

-- delete from table_1;

insert into t1_t2 ( fk_1, fk_2 )
insert into table_1 ( txt )
values ( 'A' ), ( 'B' ), ( 'C' )
returning id_1, 42;

The inner insert works and dumps the inserted ids along with the
constant which is needed in the outer insert as reference to the project.

Both inserts run together give an error.

On Sun, Oct 3, 2010 at 8:31 PM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
>
> insert into t1_t2 ( fk_1, fk_2 )
>    insert into table_1 ( txt )
>    values ( 'A' ), ( 'B' ), ( 'C' )
>    returning id_1, 42;
>
> The inner insert works and dumps the inserted ids along with the constant
> which is needed in the outer insert as reference to the project.
>
> Both inserts run together give an error.

Yeah, it's not capable of directly feeding the next insert like that.
You run the first insert, get the results back, then cycle through
them in your code to make the new inserts based on that.

--
To understand recursion, one must first understand recursion.