我在数据库中插入一个用户。然后我需要生成的用户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
1条答案
按热度按时间6yt4nkrj1#
你不需要左边的连接
users
自do_insert
是相同的表,但已更新。在你的选择中do_insert.id
而不是t1.id
并摆脱用户左加入,它应该工作。