Postgres Multiple WITH块在一个查询中执行多个插入和更新

Question

我的数据库有3个表、用户、公司和存储。藏匿物属于用户，用户属于公司。用户还拥有当前活动存储的外键。

我想在新用户注册时运行一个查询，为他们创建一家新公司，创建一个新用户，创建新的存储，并将该存储设置为活动。这是我所拥有的，它运行了，但它没有正确设置新用户的活动隐藏参数。

如果我构建这个查询的方式不是最有效的，我希望得到一些帮助，也只是一般性的建议。谢谢

WITH insert1 AS (
INSERT INTO firms (name) VALUES ('Jack_testFirm') RETURNING id AS ret_firm_id
)
,
insert2 AS (
INSERT INTO users (email, admin, firm_id) 
SELECT '[e-mail]', TRUE, ret_firm_id 
FROM insert1
RETURNING users.id AS new_user_id
)
,
insert3 AS (
INSERT INTO stashes (name, user_id)
SELECT 'Stash 1', new_user_id FROM insert2
RETURNING stashes.id AS new_stash_id
)
UPDATE users
SET active_stash = (SELECT new_stash_id FROM insert3)
WHERE users.id = (SELECT new_user_id FROM insert2)

查询后，我应该有一个全新的用户[e-mail]'属于公司'Jack_testFirm'，一个新的藏匿处属于'[e-mail]'和[e-mail]应该将该隐藏id作为活动隐藏存储存储在users表中。

Answer 1

使用过程。想法是首先实现所有的插入操作，使用变量hold您的new_stash_id和new_user_id，然后进行更新
过程都发生在一个事务中(存储过程在Postgres的数据库事务中运行吗？(，全部失败或全部成功
dbfiddle

调用过程：call test()

代码：

CREATE OR REPLACE PROCEDURE test ()
LANGUAGE plpgsql
AS $$
DECLARE
_new_stash_id bigint;
_new_user_id bigint;
BEGIN
WITH insert1 AS (
INSERT INTO firms (name)
VALUES ('Jack_testFirm')
RETURNING
firm_id AS ret_firm_id
), insert2 AS (
INSERT INTO users (email, admin, firm_id)
SELECT
'[e-mail]',
TRUE,
ret_firm_id
FROM
insert1
RETURNING
users.user_id AS new_user_id
),
insert3 AS (
INSERT INTO stashes (name, user_id)
SELECT
'Stash 1',
new_user_id
FROM
insert2
RETURNING
new_stash_id,
user_id
)
SELECT
new_stash_id,
user_id
FROM
insert3 INTO _new_stash_id,
_new_user_id;
RAISE NOTICE '_new_stash_id: %', _new_stash_id;
RAISE NOTICE '_new_user_id: %', _new_user_id;
UPDATE
users
SET
active_stash = _new_stash_id
WHERE
user_id = _new_user_id;
END
$$;