Postgres Insert select using WITH clause

Question

I'm trying to create some entries by using an INSERT ... SELECT ..., but also need to use the primary key from the 1st INSERT operation to be included as part of a secondary operation:

WITH dogEntries as (INSERT INTO dog (id, another_id, name, date_created)
    SELECT public.uuid_generate_v4(), 'efd55343', name, date_created
    FROM dog WHERE owner_id = '8921571' RETURNING id as dog_uuid)

INSERT INTO dog_toys (dog_id, bed_id, status)
SELECT (SELECT dog_uuid FROM dogEntries), bed_id, status FROM dog_toys 
WHERE dog_id IN(SELECT dog_id FROM dogs WHERE another_id = '21571');

public.uuid_generate_v4() will generate the id column.

But I'm getting an error, not sure but I guess is because the WITH dogEntries is of course returning multiple entries.

The error:

[21000] ERROR: more than one row returned by a subquery used as an expression

Is there any alternative of returning and iterate over the dog table inserted rows, or maybe a hint on how to traverse each of the entries from the WITH dogEntries statement?

Answer 1

(SELECT dog_uuid FROM dogEntries) in the SELECT list of the 2nd INSERT causes the error message as it returns multiple rows. It might work like this:

WITH dogentries AS (
   INSERT INTO dog (id, another_id, name, date_created)
   SELECT public.uuid_generate_v4(), 'efd55343' name, date_created
   FROM   dog
   WHERE  owner_id = 8921571
   RETURNING id AS dog_uuid
   )
INSERT INTO dog_toys (dog_id, bed_id, status)
SELECT de.dog_uuid, dt.bed_id, dt.status
FROM   dogentries de
JOIN   dog d ON d.another_id = 21571
JOIN   dog_toys dt USING (dog_id);

This is a shot in the dark. The objective is not entirely clear, and table definitions are undisclosed.