PostgreSQL with recursion

In PostgreSQL, the WITH clause provides a way to write the auxiliary reports and queries used in a large query. It helps break down complex and large queries in a simple and easy-to-read form.

1. Build a table
[SQL]View PlainCopy

1. postgres=# Create table tb9 (ID serial primary key,name character varying, parentid   Integer);
2. CREATE TABLE

[SQL]View PlainCopy

2. postgres=# \d TB9
4. Table "public.tb9"
6. Column |                    Type | Modifiers
8. ----------+-------------------+--------------------------------------------------
10. ID | Integer | Not null default Nextval (' tb9_id_seq ':: regclass)
12. name |   Character Varying |
14. ParentID |   Integer |
16. Indexes:
18. "Tb9_pkey" PRIMARY KEY, btree (ID)

2. Inserting test data
[SQL]View PlainCopy

2. postgres=# INSERT INTO tb9 values (generate_series (1,5),' John ', 0);
4. INSERT 0 5
6. postgres=# INSERT INTO tb9 values (6,' john1 ', 1);
8. INSERT 0 1
10. postgres=# INSERT INTO tb9 values (7,' John2 ', 1);
12. INSERT 0 1
14. postgres=# INSERT INTO tb9 values (8,' John11 ', 6);
16. INSERT 0 1

[SQL]View PlainCopy

2. postgres=# SELECT * from tb9;
4. ID | name | ParentID
6. ----+--------+----------
8. 1 |        John | 0
10. 2 |        John | 0
12. 3 |        John | 0
14. 4 |        John | 0
16. 5 |        John | 0
18. 6 |        john1 | 1
20. 7 |        john2 | 1
22. 8 |        John11 | 6
24. (8 rows)

3. With clause
[SQL]View PlainCopy

1. postgres=# with t as (select * from tb9 where parentid=1) select count (0) from t;
2. Count
3. -------
4. 2
5. (1 row)

[SQL]View PlainCopy

1. postgres=# with t (a,b,c) as (select * from tb9 where parentid=1) select a,b,c from t;
2. A | B | C
3. ---+-------+---
4. 6 | john1 | 1
5. 7 | john2 | 1
6. (2 rows)

4. Combined use of multiple with clauses
Parentid=1 all child records of the record
[SQL]View PlainCopy

1. postgres=# with t1 as  (select * from tb9), T2  as (select * from tb9  where parentid=1)  select t1.* from t1,t2 WHERE T2.ID=T1.PARENTID;  
2.  id |  name  | parentid   
3. - ---+--------+----------  
4.   8 | john11 |         6  
5. (1 row)   

5. Recursion
All child records for records with ID 1
[SQL]View PlainCopy

2. postgres=# with recursive T as (select ID,name,parentid from tb9 where id=1 UNION ALL select k.id,k. name,k.parentid from tb9 k,t where T.id=k.parentid) select * from t;
4. ID | name | ParentID
6. ----+--------+----------
8. 1 |        John | 0
10. 6 |        john1 | 1
12. 7 |        john2 | 1
14. 8 |        John11 | 6
16. 9 |        John21 | 7
18. (5 rows)

Transfer from http://blog.csdn.net/luojinbai/article/details/44015581

PostgreSQL with recursion