Insert multiple rows using single value from previous query

Question

I have a one-to-many relationship between two tables, where IDs are automatically generated on insertion. I want to insert a single row into the "parent" table, then use the generated ID of that row as a foreign key when I insert multiple rows into the "children" table.

For example, say I have this schema:

CREATE TABLE users (
 id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (),
 username TEXT NOT NULL,
);
CREATE TABLE posts (
 id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (),
 author_user_id UUID NOT NULL REFERENCES users (id),
 body TEXT,
);

In postgres I can insert multiple rows using VALUES:

INSERT INTO
 posts (author_user_id, body)
VALUES
 (<some uuid here>, "Hello, World!"),
 (<some uuid here>, "I'm hungry.");

I can also insert a user and a single post referencing that author using a common table expression:

WITH john AS (
 INSERT INTO
 users (username)
 VALUES
 (john_smith)
 RETURNING
 id
)
INSERT INTO
 posts (author_user_id, body)
SELECT
 john.id, "Hello, World!"
FROM
 john;

Is there a way I can combine the two? i.e. insert a single user, get the ID of that new row, and use that ID when inserting multiple posts?

It doesn't have to be via a CTE if there's a simpler way. Thanks!

Answer 1

WITH
cte1 (author_user_id) AS (
 INSERT INTO users (username) 
 VALUES ('John Smith')
 RETURNING id
 ),
cte2 (body) AS (
 VALUES ('Text 1'), ('Text 2')
 )
INSERT INTO posts (author_user_id, body)
SELECT cte1.author_user_id, cte2.body
FROM cte1
CROSS JOIN cte2;

or

WITH
cte (author_user_id) AS (
 INSERT INTO users (username) 
 VALUES ('John Smith')
 RETURNING id
 )
INSERT INTO posts (author_user_id, body)
SELECT cte.author_user_id, subq.body
FROM cte
CROSS JOIN (
 VALUES ('Text 1'), ('Text 2')
 ) AS subq (body);