Postgres中的Common Table Expression

Common Table Expression 是 pg 里极为重要的特性。这个特性简单的说就是 INSERT/UPDATE/DELTE 三项操作可以返回结果集。如：

update item set state = ‘A’ where state = ‘W’ returning *

update 语句通常视为 execute，而在 CTE 技术中却可以当做 query。

那么 CTE 有什么用呢？

1. 可以用于解决 upsert 问题。

Upserting via Writeable CTE - zillablog
http://xzilla.net/blog/2011/Mar/Upserting-via-Writeable-CTE.html

2. 可以避免先查询后更新的并发问题。

刚才看到有代码这样先查询符合条件的记录，再更新符合条件的记录：

select * from xx where cond

update xx set state = b where cond

先后执行两次查询，两次查询虽然是同一个cond，但前后2个查询之间可能发生难以预料的变更，命中的行集可能不同。改用 CTE 后，update 直接返回命中的行集，可消除因为前后查询不一致导致的并发问题。

3. 解决自动生成ID的取值问题。

在 oracle 里，对于自动递增主键 id，通常要先对 sequence 取下一个 id，然后再 insert，这个做法需要两次查询，效率不高，且对有 default 值的，或者 value 为函数结果的（如 sysdate），仍然无法得到新的值，往往还要执行一次 select 获得新增行的完整数据。

也就是说对于自动生成ID数据表要获得新行ID需要如下3步：

new id = select seq.nextval
insert into t (id, ..) values (new id)
select * from t where id = new id

对于一次插入多行的场景， 如（insert t select），要想得到新插入的行在 oracle 等数据库里更难表达。

引入CTE后，只需要在 insert 后加上 returning *，insert 动作就能返回新插入行的完整数据。

 

上面3点都是技术性问题，CTE 的重要意义不仅于此，CTE 真正的意义在于它彻底打通了所有的关系运算，利用 cte + 临时表 + with，大部分存储过程都不再需要，一个查询分步演算，就能解决所有问题——是所有问题！不是大部分问题！如对关系运算有充分理解可知此说不虚！

---

如欲对关系运算做更多了解，强烈推荐阅读这本：

《深度探索关系数据库:实践者的关系理论》(（美）戴特 著)【简介_书评_在线阅读】 - 当当图书
http://product.dangdang.com/9309739.html?_ddclickunion=P-295132-156777_64_0__1|ad_type=0|sys_id=1#dd_refer=http%3A%2F%2Fc.duomai.com%2Ftrack.php%3Fsite_id%3D156777%26aid%3D64%26euid%3D%26t%3Dhttp%253a%252f%252fproduct.dangdang.com%252f9309739.html

这书很早了，到处都卖光了，自己找个PDF吧