Outer SELECT from INSERT RETURNING and inner SELECT statement

Question

I am trying to write a query that copies a row in a table to that same table, gives it a new sequential primary key, and associates it with a new foreign key. I need to associate the new primary key with another foreign key that's not inserted and exists in a different relational table (a lookup table).

I'd like to be able to do this as a single transaction, but I can't seem to find a way to associate the original row with the copied row as the unique id is new for the copied row. This is going to be a bit of a mouthful, but here's my specific question:

Can an outer SELECT clause enclose an inner INSERT with inner SELECT clause and RETURNING such that values from both the inner SELECT and the INSERT's RETURNING clause are selected and properly joined? Here's what I've attempted:

WITH batch_select AS (
    SELECT id, owner_id, 1992 AS project_id
    FROM batch
    WHERE project_id = 1921
),
batch_insert AS (
    INSERT INTO batch (owner_id, project_id)
    SELECT bs.owner_id, bs.experiment_id
    FROM batch_select bs
    RETURNING id
)
SELECT bs.id AS origin_id, bi.id AS destination_id
FROM batch_select bs, batch_insert bi;

I need the origin_id to correspond to the destination_id. Obviously right now it's just a CROSS JOIN where everything is paired with everything and isn't very useful. I'd also be using the results of the last SELECT statement to run the INSERT into the lookup table, something like this (batch_join_select query could be implemented in the last insert, but has been left for clarity):

WITH batch_select AS (
    SELECT id, owner_id, 1992 AS project_id
    FROM batch
    WHERE project_id = 1921
),
batch_insert AS (
    INSERT INTO batch (owner_id, project_id)
    SELECT bs.owner_id, bs.experiment_id
    FROM batch_select bs
    RETURNING id
),
batch_join_select AS (
    SELECT bs.id AS origin_id, bi.id AS destination_id
    FROM batch_select bs, batch_insert bi
)
INSERT INTO lookup_batch_container (batch_id, container_id)
SELECT bjs.destination_id, lbc.container_id
FROM batch_join_select bjs
INNER JOIN lookup_batch_container lbc ON lbc.batch_id = bjs.origin_id;

I found a similar question on the dba exchange, but the accepted answer doesn't correctly associate the two when there's more than one row.

Do I just have to do this with several transactions?

[EDIT] Adding some minimal schema:

Table lookup_batch_container
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 batch_id     | integer | not null
 container_id | integer | not null

Indexes:
    "lookup_batch_container_batch_id_container_id_key" UNIQUE CONSTRAINT, btree (batch_id, container_id)
Foreign-key constraints:
    "lookup_batch_container_batch_id_fkey" FOREIGN KEY (batch_id) REFERENCES batch(id) ON DELETE CASCADE
    "lookup_batch_container_container_id_fkey" FOREIGN KEY (container_id) REFERENCES container(id) ON DELETE CASCADE

Table batch
      Column      |            Type             |                                     Modifiers                                      
------------------+-----------------------------+------------------------------------------------------------------------------------
 id               | integer                     | not null default nextval('batch_id_seq'::regclass)
 owner_id         | integer                     | not null
 project_id       | integer                     | not null

Indexes:
    "batch_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "batch_project_id_fkey" FOREIGN KEY (project_id) REFERENCES project(id) ON DELETE CASCADE
    "batch_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES owner(id) ON DELETE CASCADE
Referenced by:
    TABLE "lookup_batch_container" CONSTRAINT "lookup_batch_container_batch_id_fkey" FOREIGN KEY (batch_id) REFERENCES batch(id) ON DELETE CASCADE

Table container
        Column         |            Type             |                                  Modifiers                                   
-----------------------+-----------------------------+------------------------------------------------------------------------------
 id                    | integer                     | not null default nextval('stirplate_source_file_container_id_seq'::regclass)
 owner_id              | integer                     | not null
 status                | container_status_enum       | not null default 'new'::container_status_enum
 name                  | text                        | not null

Indexes:
    "container_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "container_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES owner(id) ON DELETE CASCADE
Referenced by:
    TABLE "lookup_batch_container" CONSTRAINT "lookup_batch_container_container_id_fkey" FOREIGN KEY (container_id) REFERENCES container(id) ON DELETE CASCADE

Answer 1

with batch_select as (
    select id, owner_id, 1992 as project_id
    from batch
    where project_id = 1921
), batch_insert as (
    insert into batch (owner_id, project_id)
    select owner_id, project_id
    from batch_select
    order by id
    returning *
)
select unnest(oid) as oid, unnest(did) as did
from (
    select
        array_agg(distinct bs.id order by bs.id) as oid,
        array_agg(distinct bi.id order by bi.id) as did
    from
        batch_select bs
        inner join
        batch_insert bi using (owner_id, project_id)
) s
;
 oid | did 
-----+-----
   1 |   4
   2 |   5
   3 |   6

Given this batch table:

create table batch (
    id serial primary key,
    owner_id integer,
    project_id integer
);
insert into batch (owner_id, project_id) values
(1,1921),(1,1921),(2,1921);

It could be simpler if the primary key were (id, owner_id, project_id). Isn't it?