PostgreSQL traverses the simple tree, postgresql

PostgreSQL traverses the simple tree, postgresql
Yesterday I used MySQL to implement the ORACLE recursive statement CONNECT BY, which looks a little complicated. Today, let's take a look at how POSTGRESQL implements oracle connect.
We still use the same table and data from yesterday. POSTGRESQL is the most self-proclaimed ORACLE database, so most of the statements can be simple and disguised.
At this point, you can use its own WITH recursion function, and connect by functions similar to third-party extensions.


First, let's look at the first point and use 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.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 provided BY tablefunc extension to traverse the tree.
Because the two tables designed yesterday are associated by IDS, It is troublesome to display the names of the built-in functions of this extension, simply put, I used a temporary table to save the desired result.

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

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 provided BY TABLEFUNC extension to traverse this tree.

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

Simplest binary tree traversal

The pre-order is followed by the root, left, and right, followed by the left, right, and followed by the root. Just read the book and understand it. This name must have something to do with the method, but it is quite understandable.

The establishment and traversal of a simple binary tree

# Include "stdio. h"
# Include "stdlib. h"
Typedef struct btnode
{
Int data;
Struct btnode * lc, * rc;
} Btnode, * bitree;
Bitree precreate ()
{
Int ch; bitree root;
Scanf ("% d", & ch );
If (ch = 0)
Root = NULL;
Else
{
Root = (bitree) malloc (sizeof (btnode ));
Root-> data = ch;
Root-> lc = precreate ();
Root-> rc = precreate ();
}
Return root;
}
Void preorder (bitree root)
{
If (root! = NULL)
{
Preorder (root-> lc );
Preorder (root-> rc );
Printf ("% d", root-> data );
}
}
Void main ()
{
Bitree;
A = precreate ();
Preorder ();
}
You have changed it. Contact me if you have any questions! In addition, your traversal function is first traversed in order and helps you change it. You can try it.