A tutorial on the method of PostgreSQL traversing a simple tree

Source: Internet
Author: User
Tags postgresql


It is also the same table and data that used the last MySQL stored procedure to implement the Oracle Adjacency model tree processing method instance. PostgreSQL prides itself on being the most Oracle-like database, so most of the statements can be simple and implemented in disguise.



At this point, you can use the with recursive function of his own, and you can also use a third party extension to bring similar connect by function.






First look at the 1th, using recursive with to show the path of this tree.


The code is as follows


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 in (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
(rows)


time:3.260 ms







You can also traverse the tree by using the Connect by function brought by the tablefunc extension.
Since the two tables that were designed yesterday were associated by ID, this extended self-contained function would be more cumbersome to show the name, and I would have used a temporary table to save the results I wanted.


The code is as follows


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=#





The corresponding ID, name, is updated here.


The code is as follows

t_girl=# Update tmp_country_relation Set parentname = A.name from country as a where parentid = a.id;
UPDATE 15
time:1.829 ms





I used the tablefunc extension to bring the connect by to the traversal of this tree.


The code is as follows


t_girl=# Select path from Connectby (' tmp_country_relation as a ', ' a.name ', ' a.parentname ', ' earth ', 0, '/') 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
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
(rows)


time:5.974 ms
t_girl=#







Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.