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.