我的问题是Erwin Brandstetter在
this thread关于正确使用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是否为空.所以我的理论方法是这样的:
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告诉我:
Syntax error at or near “INSERT INTO port (z_id)”