I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key.
What I'm doing is more complicated than the following but this simple example demonstrates what I want to do.
So let's say I have a temporary table temp_import defined by :
create temp_import(
row_id int,
name varchar(32),
email varchar(255)
)
I have two target tables defined by :
create users (
userid int not null default nextval('tmp_uid_seq'::regclass),
username varchar(32)
)
create emails (
userid int,
email varchar(255)
)
Currently I have some plpython which inserts a row at a time into users returning userid, followed by a second insert which uses the returned userid to populate emails.
loop:
insert into users (username) values 'Blah' returning userid
insert into emails (userid, email) values (userid, 'Blah2')
goto loop
This works OK but I want to extract better performance by doing set based operations.
I want to create a temporary mapping table :
create table mapping (
row_id int,
userid int
)
and try to do something like :
with rows as (insert into users (username)
select username from temp_import returning userid, row_id)
insert into mapping (userid, row_id)
The issue here is that I cannot return row_id as I am not selecting it for insert into the target table
If I could do this I could populate the emails table in one go:
insert into emails (userid,email)
select userid,email from temp_import t, mapping m
where t.row_id = m.row_id
Anyone know how I can return the row_id in the with rows as statement above?
Thanks in advance.