PostgreSQL嵌套INSERT / WITHs用于外键插入 [英] PostgreSQL nested INSERTs / WITHs for foreign key insertions

问题描述

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

I'm using PostgreSQL 9.3, and I'm trying to write a SQL script to insert some data for unit tests, and I've run into a bit of a problem.

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

Let's say we have three tables, structured like this:

------- 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 id 列的外键c>和 B 。

我想做的是在每个行中插入一行这三个使用纯SQL的表，而没有将ID硬编码到SQL中（在运行此脚本之前对数据库进行假设似乎是不可取的，因为如果这些假设发生变化，我将不得不返回并重新计算正确的ID对于所有测试数据）。

What I'm trying to do is to insert a row into each of these three tables with pure SQL, without having the ID's hard-coded into the SQL (making assumptions about the database before this script is run seems undesirable, since if those assumptions change I'll have to go back and re-compute the proper ID's for all of the test data).

请注意，我的确意识到我可以通过编程方式执行此操作，但是总的来说，编写纯SQL不会比编写程序代码来执行那么冗长SQL，因此对于测试套件数据更有意义。

Note that I do realize I could do this programatically, but in general writing pure SQL is way less verbose than writing program code to execute SQL, so it makes more sense for test suite data.

无论如何，这是我编写的查询，我认为这样可以起作用：

Anyway, here's the query I wrote which I figured would work:

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告诉我：

However, this doesn't work, and results in PostgreSQL telling me:

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

在没有硬编码ID值的情况下，一般是否可以编写这种类型的查询的正确方法？

Is there a correct way to write this type of query in general, without hard-coding the ID values?

（您可以在此处找到一个小提琴，其中包含此示例。）

(You can find a fiddle here which contains this example.)

推荐答案

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

Don't nest the common table expressions, just write one after the other:

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;

这篇关于PostgreSQL嵌套INSERT / WITHs用于外键插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！