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.

You can save the result to a temporary table and re-use the temporary table. — Gordon Linoff
– Gordon Linoff, Commented May 27, 2015 at 16:06

Gordon Linoff · Accepted Answer · 2015-05-27 16:10:35Z

3

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.

answered May 27, 2015 at 16:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3550283 Over a year ago

Thanks. btw the next RETURNING clauses are not needed.

gsb22 Over a year ago

@user3550283 were you able you get this thing working? I'm still facing issue.

Gordon Linoff Over a year ago

@GauriShankarBadola . . . You should perhaps ask your own question.

gsb22 Over a year ago

@GordonLinoff Actually I was and then SO suggested me this. I have the very same issue and this thing doesn't work for me. :(

Collectives™ on Stack Overflow

Using a single WITH multiple times in postgresql query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related