sql - How to copy rows into a new a one to many relationship -

i'm trying copy set of data in 1 many relationship create new set of same data in new, unrelated 1 many relationship. lets call them groups , items. groups have 1-* relation items - 1 group has many items.

i've tried create cte this, can't items inserted (in y) newly inserted groups don't have items associated them yet. think need able access old. , new. in trigger, can't work out how this.

i think solve introducing previous parent id templateitem table, or maybe temp table data required enable me join on that, wondering if possible solve way?

sql fiddle keeps breaking on me, i've put code here well:

drop table if exists meta.templateitem; drop table if exists meta.templategroup;  create table meta.templategroup (  templategroup_id serial primary key,  groupname text,  roworder int );  create table meta.templateitem (  templateitem_id serial primary key,  itemname text,  templategroup_id integer not null references meta.templategroup(templategroup_id) );  insert meta.templategroup (groupname, roworder) values ('group1', 1), ('group2', 2); insert meta.templateitem (itemname, templategroup_id) values ('item1a',1), ('item1b',1), ('item2a',2);    x (     insert meta.templategroup (groupname, roworder)          select distinct groupname || '_v1' meta.templategroup templategroup_id in (1,2)         returning groupname, templategroup_id, roworder     ), y (     insert meta.templateitem (itemname, templategroup_id)         select itemname, x.templategroup_id         meta.templateitem          inner join x on x.templategroup_id = i.templategroup_id         returning *     ) select * y; 

use auxiliary column templategroup.old_id:

alter table meta.templategroup add old_id int;  x (     insert meta.templategroup (groupname, roworder, old_id)          select distinct groupname || '_v1', roworder, templategroup_id         meta.templategroup          templategroup_id in (1,2)         returning templategroup_id, old_id     ), y (     insert meta.templateitem (itemname, templategroup_id)         select itemname, x.templategroup_id         meta.templateitem          inner join x on x.old_id = i.templategroup_id         returning *     ) select * y;   templateitem_id | itemname | templategroup_id  -----------------+----------+------------------                4 | item1a   |                3                5 | item1b   |                3                6 | item2a   |                4 (3 rows)     

it's impossible in single plain sql query without additional column. have store old ids somewhere. alternative can use plpgsql , anonymous code block:

before:

select * meta.templategroup join meta.templateitem using (templategroup_id);   templategroup_id | groupname | roworder | templateitem_id | itemname  ------------------+-----------+----------+-----------------+----------                 1 | group1    |        1 |               1 | item1a                 1 | group1    |        1 |               2 | item1b                 2 | group2    |        2 |               3 | item2a (3 rows) 

insert:

do $$ declare     grp record; begin     grp in         select distinct groupname || '_v1' groupname, roworder, templategroup_id         meta.templategroup          templategroup_id in (1,2)     loop         insert_group (             insert meta.templategroup (groupname, roworder)              values (grp.groupname, grp.roworder)             returning templategroup_id         )         insert meta.templateitem (itemname, templategroup_id)             select itemname || '_v1', g.templategroup_id             meta.templateitem             join insert_group g on grp.templategroup_id = i.templategroup_id;     end loop; end $$; 

after:

select * meta.templategroup join meta.templateitem using (templategroup_id);   templategroup_id | groupname | roworder | templateitem_id | itemname   ------------------+-----------+----------+-----------------+-----------                 1 | group1    |        1 |               1 | item1a                 1 | group1    |        1 |               2 | item1b                 2 | group2    |        2 |               3 | item2a                 3 | group1_v1 |        1 |               4 | item1a_v1                 3 | group1_v1 |        1 |               5 | item1b_v1                 4 | group2_v1 |        2 |               6 | item2a_v1 (6 rows)