Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to traverse the tree with PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Yesterday I used MySQL to implement the recursive statement CONNECT BY of ORACLE, which looks a little more complicated. Today let's take a look at how POSTGRESQL implements ORACLE's CONNECT BY.

Still use the same tables and data as yesterday. POSTGRESQL boasts that it is most like an ORACLE database, so most statements can be implemented simply and in a disguised form.

At this point, you can use his own WITH recursion function, and you can also use similar connect by functions brought by third-party extensions.

Let's take a look at the first point, using recursive WITH to show the path of the tree.

T_girl=# with recursive tmp_country (id,path) as t_girl-# (t_girl (# select a.id) b.id'| | b.name as "path" from country_relation as an inner join country as b on (a.id = b.id) where a.parentid is nullt_girl (# union allt_girl (# select a.id) Q.path | |'/'| | b.name as "path" from country_relation as an 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

You can also traverse the tree with the CONNECT BY function that comes with the tablefunc extension.

Since the two tables designed yesterday are related through ID, it is troublesome for the function included in this extension to show the name, so here I use a temporary table to save the results I want.

T_girl=# CREATE TEMPORARY TABLE tmp_country_relation as SELECT b.idrea.namereparentic b.parentid.parentid; SELECT 16Time: 11.773 mst_girl=#; t_girl=# CREATE TEMPORARY TABLE tmp_country_relation as SELECT b.idrea.name.id.namerea.name.reparentid.parentid.parentid.

Here, the corresponding ID is updated to NAME.

T_girl=# update tmp_country_relation set parentname = a.name from country as a where parentid = A.ID. Update 15Time: 1.829 ms

I use the CONNECT BY brought by the TABLEFUNC extension to traverse the tree.

T_girl=# select path from connectby (as g (id text,parentid text,level int,path text) order by level) t_girl=# select path from connectby ('parentnamejewelry') as g (id text,parentid text,level int,path text) 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 Earth/North America/United States Earth/North America/United States/California Earth/North America/United States/Arkansas Earth/North America/United States/Alabama Earth/North America/United States/Alaska Earth/North America/United States/Arizona (16 rows) Time: 5.974 mst_girl=# "how to traverse a tree with PostgreSQL" ends here Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.