Postgres中带WITH语句和模式标识符的UPDATE查询无法执行？

问题背景 #

我最近写SQL时碰到个挺折腾的问题：想用WITH语句把一个查询的结果传递给后面两个查询，结果执行时一直报语法错误，错误提示精准指向某一行。但我把语句里的(SELECT rp.fid FROM rp)替换成任意整数，单独跑后续查询却完全正常，一开始还怀疑是模式标识符搞的鬼，折腾半天终于找到原因了。我用的是PostgreSQL 9.6.6版本。

解决方法 #

其实问题特别简单，只需要把WITH后面的第一个查询用括号包裹起来，再给它命名一个变量（比如x）就搞定了！

举个直观的例子，原来错误的写法大概是这样：

WITH SELECT rp.fid FROM rp
SELECT * FROM table1 WHERE id = (SELECT fid FROM rp);
SELECT * FROM table2 WHERE ref_id = (SELECT fid FROM rp);

改成正确的写法后：

WITH x AS (SELECT rp.fid FROM rp)
SELECT * FROM table1 WHERE id = (SELECT fid FROM x);
SELECT * FROM table2 WHERE ref_id = (SELECT fid FROM x);

原因分析 #

PostgreSQL的WITH子句对公共表表达式（CTE）的定义有明确要求：每个CTE必须有专属名称，且子查询需要用AS和括号包裹起来。原来的写法既没给第一个子查询命名，也没按规范包裹，导致数据库解析器根本识别不出这是个可被后续查询引用的CTE，自然就抛出了语法错误。而替换成整数后，后续查询变成了简单的常量匹配，所以能正常运行。

内容的提问来源于stack exchange，提问作者wfgeo