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);