Using a single WITH multiple times in postgresql query

Question

How can I use my first with statement multiple times? With a code like below I can't use it for any other statement than the one that comes right after it.

WITH insertuser AS (

    INSERT INTO 
    zorro.user (username, firstname, lastname,
        accountstatus, roles, creationdatetime)
    VALUES('test', 'test', 'test',
        'test', 'test', current_timestamp)
    RETURNING id
    )

INSERT INTO
zorro.email (address, confirmed, count, user_id)
SELECT 'test', false, 1, id
FROM insertuser;

INSERT INTO
zorro.password (hash, count, user_id)
SELECT 'test', 1, id
FROM insertuser;

INSERT INTO
zorro.phone_number (number, confirmed, count, user_id)
SELECT 'test', false, 1, id
FROM insertuser;

INSERT INTO
zorro.Question (text, answer, count, user_id)
SELECT 'test', 'test', 1, id
FROM insertuser;

I get sql error at line 19, near the second "FROM insertuser" in the code.

Answer 1

I think you can use a series of common table expressions:

WITH insertuser AS (
    INSERT INTO zorro.user (username, firstname, lastname, accountstatus, roles, creationdatetime)
        VALUES('test', 'test', 'test', 'test', 'test', current_timestamp)
     RETURNING id
    ),
    em as (
     INSERT INTO zorro.email (address, confirmed, count, user_id)
         SELECT 'test', false, 1, id
         FROM insertuser
     RETURNING *
    ),
    p as (
     INSERT INTO zorro.password (hash, count, user_id)
         SELECT 'test', 1, id
         FROM insertuser
     RETURNING *
    ),
    pn as (
     INSERT INTO zorro.phone_number (number, confirmed, count, user_id)
         SELECT 'test', false, 1, id
         FROM insertuser
     RETURNING *
   )
INSERT INTO zorro.Question (text, answer, count, user_id)
    SELECT 'test', 'test', 1, id
    FROM insertuser;

I am not 100% sure if the RETURNING clause is needed for the CTEs.