PostgreSQL嵌套插入/使用WITH子句进行外键插入

Question

我正在使用PostgreSQL 9.3，尝试编写一个SQL脚本来插入一些用于单元测试的数据，但遇到了一点问题。

假设我们有三个表，结构如下：

------- Table A -------    -------- Table B --------    -------- Table C --------
id  | serial NOT NULL      id   | serial NOT NULL       id   | serial NOT NULL
foo | character varying    a_id | integer NOT NULL      b_id | integer NOT NULL
                           bar  | character varying     baz  | character varying

B.a_id 和 C.b_id 这两列是分别指向表 A 和 B 的 id 列的外键。

我的目标是在不将 ID 硬编码到 SQL 中（在运行此脚本之前对数据库进行假设似乎不太理想，因为如果这些假设发生变化，我将不得不重新计算所有测试数据的正确 ID）的情况下，使用纯 SQL 向这三个表中插入一行数据。

请注意，我确实意识到可以通过编程方式实现这一点，但通常编写纯 SQL 要比编写执行 SQL 的程序代码更简洁明了，因此对于测试套件数据而言，使用纯 SQL 更为合理。

无论如何，以下是我编写的查询语句，我认为它会起作用：

WITH X AS (
    WITH Y AS (
        INSERT INTO A (foo)
        VALUES ('abc')
        RETURNING id
    )
    INSERT INTO B (a_id, bar)
    SELECT id, 'def'
    FROM Y
    RETURNING id
)
INSERT INTO C (b_id, baz)
SELECT id, 'ghi'
FROM X;

然而，这样做不起作用，并导致PostgreSQL告诉我：

ERROR:  WITH clause containing a data-modifying statement must be at the top level

一般来说，有没有一种正确的方式可以编写此类型的查询，而不需要硬编码ID值？

（您可以在这里找到包含此示例的小工具。）

- CmdrMoozy

---

最好发布“创建表”语句而不是原始结构，这样我们更容易通过你的表创建测试环境。 - user330315

我实际上已经在这个例子中制作了一个小测试，但当我回来编辑它时，您已经回答了我的问题。将来，我会把它放在前面。 - CmdrMoozy

只能有一个带语句的There can only be one! - Denis de Bernardy

Answer 1

不要嵌套公共表达式，只需一个接一个地编写：

WITH Y AS (
  INSERT INTO A (foo)
  VALUES ('abc')
  RETURNING id
), X as (
  INSERT INTO B (a_id, bar)
  SELECT id, 'def'
  FROM Y
  RETURNING id
)
INSERT INTO C (b_id, baz)
SELECT id, 'ghi'
FROM X;

- user330315

---

这个应该很明显，但是我完全没有想到。谢谢！ :) - CmdrMoozy