PostgreSQL嵌套INSERT / WITHs用于外键插入

本文介绍了PostgreSQL嵌套INSERT / WITHs用于外键插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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 和 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;