I insert a user into the database. Then I need the user id generated to insert into a teamMember
table. I am using CTE’s, I read they run concurrently, is it possible to return the id of the inserted users to the next statement?
with INPUT (col) as ( VALUES (' { "insert":[ {"username":"JSmith", "name":"John Smith", "mail":"JSmith@smith.com","jobTitle":"Lead","hasImage":true,"teamId":47} ], "delete":[ ], "update":[{"username":"JDoe", "name":"Jane Doe","mail":"JDoe@mark.com","jobTitle":"Software Developer","hasImage":false, "uid": 255, "teamId":40}] }'::jsonb) ), -- Insert new users if username is unique do_insert as ( INSERT INTO users (username, name, mail, "jobTitle", "hasImage") SELECT i.value ->> 'username', i.value ->> 'name', i.value ->> 'mail', i.value ->> 'jobTitle', (i.value ->> 'hasImage')::boolean FROM INPUT CROSS JOIN json_array_elements(to_json(col -> 'insert')) i WHERE NOT EXISTS ( SELECT (username) FROM users WHERE username = i.value ->> 'username' ) RETURNING * ) -- Insert row in teamMembers INSERT INTO "teamMembers"("teamId", "userId") select (i.value ->> 'teamId')::int, t1.id from input cross join json_array_elements(to_json(col -> 'insert')) i left join users t1 ON i.value ->> 'username' = t1.username left join do_insert on users.username = do_insert.username
Advertisement
Answer
You don’t need the left join on users
since do_insert
is the same table but updated. In your select put do_insert.id
instead of t1.id
and get rid of the users left join and it should work.
with INPUT (col) as ( VALUES (' { "insert":[ {"username":"JSmith", "name":"John Smith", "mail":"JSmith@smith.com","jobTitle":"Lead","hasImage":true,"teamId":47} ], "delete":[ ], "update":[{"username":"JDoe", "name":"Jane Doe","mail":"JDoe@mark.com","jobTitle":"Software Developer","hasImage":false, "uid": 255, "teamId":40}] }'::jsonb) ), -- Insert new users if username is unique do_insert as ( INSERT INTO users (username, name, mail, "jobTitle", "hasImage") SELECT i.value ->> 'username', i.value ->> 'name', i.value ->> 'mail', i.value ->> 'jobTitle', (i.value ->> 'hasImage')::boolean FROM INPUT CROSS JOIN json_array_elements(to_json(col -> 'insert')) i WHERE NOT EXISTS ( SELECT (username) FROM users WHERE username = i.value ->> 'username' ) RETURNING * ) -- Insert row in teamMembers INSERT INTO "teamMembers"("teamId", "userId") select (i.value ->> 'teamId')::int, do_insert.id from input cross join json_array_elements(to_json(col -> 'insert')) i left join do_insert on users.username = do_insert.username