pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed magic for inserting in 2 tables
6+ messages / 2 participants
[nested] [flat]
* Need magic for inserting in 2 tables
@ 2010-10-03 22:14 Andreas <maps.on@gmx.net>
2010-10-03 23:46 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas @ 2010-10-03 22:14 UTC (permalink / raw)
To: pgsql-sql
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 ?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need magic for inserting in 2 tables
2010-10-03 22:14 Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
@ 2010-10-03 23:46 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-10-04 00:47 ` Re: Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2010-10-03 23:46 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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
-- To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need magic for inserting in 2 tables
2010-10-03 22:14 Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
2010-10-03 23:46 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-10-04 00:47 ` Andreas <maps.on@gmx.net>
2010-10-04 00:58 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas @ 2010-10-04 00:47 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need magic for inserting in 2 tables
2010-10-03 22:14 Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
2010-10-03 23:46 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
2010-10-04 00:47 ` Re: Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
@ 2010-10-04 00:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-10-04 02:31 ` Re: Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2010-10-04 00:58 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Sun, Oct 3, 2010 at 6:47 PM, Andreas <maps.on@gmx.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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need magic for inserting in 2 tables
2010-10-03 22:14 Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
2010-10-03 23:46 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
2010-10-04 00:47 ` Re: Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
2010-10-04 00:58 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-10-04 02:31 ` Andreas <maps.on@gmx.net>
2010-10-04 02:46 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas @ 2010-10-04 02:31 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need magic for inserting in 2 tables
2010-10-03 22:14 Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
2010-10-03 23:46 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
2010-10-04 00:47 ` Re: Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
2010-10-04 00:58 ` Re: Need magic for inserting in 2 tables Scott Marlowe <scott.marlowe@gmail.com>
2010-10-04 02:31 ` Re: Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
@ 2010-10-04 02:46 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2010-10-04 02:46 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Sun, Oct 3, 2010 at 8:31 PM, Andreas <maps.on@gmx.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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-10-04 02:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-03 22:14 Need magic for inserting in 2 tables Andreas <maps.on@gmx.net>
2010-10-03 23:46 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-10-04 00:47 ` Andreas <maps.on@gmx.net>
2010-10-04 00:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-10-04 02:31 ` Andreas <maps.on@gmx.net>
2010-10-04 02:46 ` Scott Marlowe <scott.marlowe@gmail.com>
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