如何使用更新的表正确地对另外两个表进行"WITH"SQL查询

Question

我想插入到一个表中，并在另外两个表中使用生成的id。对于1+1表格，我会做：

WITH inserted AS (
INSERT INTO whatever1 (
whatever
)
VALUES('whatever')
RETURNING id
)
INSERT INTO whatever2 (
id
whatever
)
SELECT
inserted.id,
'whatever'
FROM inserted

如何在单个查询中对whatever3执行与我对whatever2相同的操作
文档中没有有用的示例：https://www.postgresql.org/docs/current/queries-with.html

我想出了(但不喜欢，因为whatever2从未使用过(：

WITH inserted1 AS (
INSERT INTO whatever1 (
whatever
)
VALUES('whatever')
RETURNING id
),
inserted2 AS (
INSERT INTO whatever2 (
id
whatever
)
SELECT
inserted1.id,
'whatever'
FROM inserted1
)
INSERT INTO whatever3 (
id
whatever
)
SELECT
inserted1.id,
'whatever'
FROM inserted1

Answer 1

您必须调用所有需要的部分。在本例中，第2节的id与第1节相同，也是您在上一条INSERT语句中使用的id。像这样的东西应该起作用：

WITH    inserted1 AS (
INSERT INTO whatever1 (whatever)
VALUES('whatever')
RETURNING 
id
),  inserted2 AS (
INSERT INTO whatever2 (id, whatever)
SELECT
inserted1.id,
'whatever'
FROM inserted1
RETURNING
id  -- actually inserted1.id
)
INSERT INTO whatever3 (id, whatever)
SELECT
inserted1.id,
'whatever'
FROM inserted2;