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

旧城等待， 2022-10-31 13:43 130阅读 0赞

昨天我用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

/Earth/South America

/Earth/Europe

/Earth/Asia

/Earth/Africa

/Earth/Australia

/Earth/North America/Canada

/Earth/North America/Central America

/Earth/North America/Island Nations

/Earth/North America/United States

/Earth/North America/United States/Alabama

/Earth/North America/United States/Alaska

/Earth/North America/United States/Arizona

/Earth/North America/United States/Arkansas

/Earth/North America/United States/California

(16 rows)

Time: 3.260 ms

还可以用tablefunc扩展带来的CONNECT BY函数把这棵树遍历出来。

由于昨天设计的两张表通过ID来关联，这个扩展自带的函数要把名字展现出来比较麻烦，索性这里我就用了一张临时表保存我想要的结果。

t\_girl=\# CREATE TEMPORARY TABLE tmp\_country\_relation as SELECT b.id,a.name,b.parentid,''::text as parentname FROM country AS a,country\_relation AS b WHERE a.id = b.id;

SELECT 16

Time: 11.773 ms

t\_girl=\#

这里更新了对应的ID为NAME。

t\_girl=\# update tmp\_country\_relation set parentname = a.name from country as a where parentid = a.id;

UPDATE 15

Time: 1.829 ms

我用TABLEFUNC扩展带来的CONNECT BY 实现这棵树的遍历。

t\_girl=\# select path from connectby('tmp\_country\_relation as a','a.name','a.parentname','Earth','/') as g(id text,parentid text,level int,path text) order by level;

path

\----------------------------------------------

Earth

Earth/Australia

Earth/North America

Earth/Africa

Earth/South America

Earth/Europe

Earth/Asia

Earth/North America/Island Nations

Earth/North America/Canada

Earth/North America/Central America