我的问题是对欧文·布兰德斯特特(Erwin Brandstetter)在这个线程中关于正确使用WITH
的出色回答的某种扩展。
我的旧查询如下所示:
WITH x AS (
INSERT INTO d (dm_id)
SELECT dm_id
FROM dm, import i
WHERE dm.dm_name = i.dm_name
RETURNING d_id
), y AS (
INSERT INTO z (d_id)
SELECT d_id
FROM x
RETURNING z_id
)
INSERT INTO port (z_id)
SELECT z_id
FROM y;
这就像一个魅力。但是现在,添加了另一个表(r
)(与表d
的结构相同),并且随之而来的是必须将d_id
或r_id
添加到表z
中的可能性。这取决于表 dm_name
或 rm_name
在表 import
中是空的。所以我的理论方法是这样的:
SELECT dm_name, rm_name
,CASE WHEN dm_name != '' THEN
WITH x AS (
INSERT INTO d (dm_id)
SELECT dm_id
FROM dm, import i
WHERE dm.dm_name = i.dm_name
RETURNING d_id
), y AS (
INSERT INTO z (d_id)
SELECT d_id
FROM x
RETURNING z_id
)
INSERT INTO port (z_id)
SELECT z_id
FROM y
END
,CASE WHEN rm_name != '' THEN
WITH x AS (
INSERT INTO r (rm_id)
SELECT rm_id
FROM rm, import i
WHERE rm.rm_name = i.rm_name
RETURNING r_id
), y AS (
INSERT INTO z (r_id)
SELECT r_id
FROM x
RETURNING z_id
)
INSERT INTO port (z_id)
SELECT z_id
FROM y
END
FROM import;
但是PostgreSQL告诉我:
"插入端口 (z_id)"处或附近出现语法错误
尽管查询的这一部分应该是正确的,因为它已经可以工作了。
我希望你能帮我解决这个问题。:)
为了更好地理解 - 这是表格结构:
CREATE TABLE import (
dm_name character varying,
rm_name character varying
-- many other columns which are not relevant
);
CREATE TABLE dm (
dm_id integer NOT NULL, -- serial
dm_name character varying
-- plus more columns
);
CREATE TABLE d (
d_id integer NOT NULL, -- serial
dm_id integer -- references dm.dm_id
-- plus more columns
);
CREATE TABLE rm (
rm_id integer NOT NULL, -- serial
rm_name character varying
-- plus more columns
);
CREATE TABLE r (
r_id integer NOT NULL, -- serial
rm_id integer -- references rm.rm_id
-- plus more columns
);
CREATE TABLE z (
z_id integer NOT NULL, -- serial
r_id integer, -- references r.r_id
d_id integer -- references d.d_id
-- plus more columns
);
CREATE TABLE port (
p_id integer NOT NULL, -- serial
z_id integer, -- references z.z_id
-- plus more columns
);
导入表不知道 ID,因为它们是在原子化过程中生成的。dm 和 rm 表适用于已从导入表中提取的设备型号。d 和 r 表用于实际设备。由于端口只能有一个 r 设备或一个 d 设备或没有,因此引入了 z-table 在端口表中只有一个字段表示所有可能性。d/r 和 dm/rm 表不能组合,因为它们根据设备类型具有不同的特殊列。
在CASE
表达式中嵌套INSERT
语句。根据我所看到的,这种完全不同的方法应该可以做到:
假设
您实际上不需要外部
SELECT
.dm_name
/rm_name
在dm
/rm
中是唯一定义的,而不是空的(<> ''
)。您应该有一个CHECK
约束来确保。z
中d_id
和r_id
的列默认值为 NULL(默认值)。
dm_name
和rm_name
相互排斥
如果两者从未同时存在。
WITH d1 AS (
INSERT INTO d (dm_id)
SELECT dm.dm_id
FROM import
JOIN dm USING (dm_name)
RETURNING d_id
)
, r1 AS (
INSERT INTO r (rm_id)
SELECT rm.rm_id
FROM import
JOIN rm USING (rm_name)
RETURNING r_id
)
, z1 AS (
INSERT INTO z (d_id, r_id)
SELECT d_id, r_id
FROM d1 FULL JOIN r1 ON FALSE
RETURNING z_id
)
INSERT INTO port (z_id)
SELECT z_id
FROM z1;
该FULL JOIN .. ON FALSE
生成一个派生表,其中包含来自 d1
的所有行,r1
为相应的其他列追加了 NULL(两者之间没有重叠)。所以我们只需要一个INSERT
而不是两个。次要优化。
dm_name
和rm_name
可以共存
WITH i AS (
SELECT dm.dm_id, rm.rm_id
FROM import
LEFT JOIN dm USING (dm_name)
LEFT JOIN rm USING (rm_name)
)
, d1 AS (
INSERT INTO d (dm_id)
SELECT dm_id FROM i WHERE dm_id IS NOT NULL
RETURNING dm_id, d_id
)
, r1 AS (
INSERT INTO r (rm_id)
SELECT rm_id FROM i WHERE rm_id IS NOT NULL
RETURNING rm_id, r_id
)
, z1 AS (
INSERT INTO z (d_id, r_id)
SELECT d1.d_id, r1.r_id
FROM i
LEFT JOIN d1 USING (dm_id)
LEFT JOIN r1 USING (rm_id)
WHERE d1.dm_id IS NOT NULL OR
r1.rm_id IS NOT NULL
RETURNING z_id
)
INSERT INTO port (z_id)
SELECT z_id FROM z1;
笔记
如果两者都不存在,这两个版本也有效。
如果SELECT
不返回行,则INSERT
不插入任何内容。
如果必须处理可能与此操作冲突的并发写入访问,则快速解决方法是在同一事务中运行此语句之前锁定所涉及的表。