"Original" PostgreSQL traversal of a simple tree

Source: Internet
Author: User

Yesterday I used MySQL to implement the Oracle recursive statement connect by, which looks slightly more complicated. See how PostgreSQL is implementing connect by for Oracle today.

or using the same table and data as yesterday. PostgreSQL prides itself on being the most Oracle-like database, so most of the statements can be implemented in a simple and disguised way.

At this point, you can use his own with recursive functionality, and you can also use a third-party extension like the Connect by function.


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

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 nullt_girl (# union allt_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


You can also use the Connect by function with the tablefunc extension to traverse the tree.

Since the two tables designed yesterday are related by ID, this extension comes with the function to show the name is more troublesome, simply I used a temporary table to save the results I want.

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 16time:11.773 mst_girl=#


The corresponding ID is updated here for 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 Tablefunc extension to implement this tree traversal.

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 (16 rows) Time: 5.974 mst_ girl=#


This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1572083

"Original" PostgreSQL traversal of a simple tree

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.