postgresql 遍历参数_PostgreSQL 对简单树的遍历

昨天我用MysqL来实现了ORACLE的递归语句CONNECT BY， 看起来稍复杂些。今天来看看POSTGREsql如何实现ORACLE的CONNECT BY。

还是用昨天同样的表以及数据。POSTGREsql自诩最像ORACLE的数据库，所以大部分语句也就都可以简单而且变相的实现了。

在这点上可以用他自己带的WITH递归功能，还可以用第三方扩展带来的类似connect by 函数。

先来看第一点，用递归的WITH来展现这棵树的路径。

t_girl=# with recursive tmp_country(id,path) as

t_girl-# (

t_girl(# select a.id,'/'||b.name as "path" from country_relation as a inner join country as b on (a.id = b.id) where a.parentid is null

t_girl(# union all

t_girl(# select a.id,q.path||'/'||b.name as "path" from country_relation as a inner join tmp_country as q on (q.id = a.parentid)

t_girl(# inner join country as b on (a.id = b.id)

t_girl(# )

t_girl-# select a.path from tmp_country as a;

path

-----------------------------------------------

/Earth

/Earth/North America