postgres使用cte插入,并在下一个子查询中使用插入的id

frebpwbc  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(347)

我在数据库中插入一个用户。然后我需要生成的用户id来插入到 teamMember table。我正在使用cte,我读到它们同时运行,是否可以将插入用户的id返回到下一个语句?

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
6yt4nkrj

6yt4nkrj1#

你不需要左边的连接 usersdo_insert 是相同的表,但已更新。在你的选择中 do_insert.id 而不是 t1.id 并摆脱用户左加入,它应该工作。

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

相关问题