Oracle to PostgreSQL start with / connect by

start with / connect by in Oracle provides the ability of hierarchical query, traversing records from START WITH and recursively querying the result set until all qualified results are obtained.

For example, the following test data:

drop table sr_menu; create table sr_menu( id number(10) not null, parent number(10), title varchar2(50) ); insert into sr_menu values (1, null, 'level 0'); insert into sr_menu values (2, 1, 'level 1'); insert into sr_menu values (3, 1, 'level 1'); insert into sr_menu values (4, 1, 'level 1'); insert into sr_menu values (5, 3, 'level 2'); commit; select * from sr_menu; ID PARENT TITLE ---------- ---------- -------------------------------------------------- 1 level 0 2 1 level 1 3 1 level 1 5 3 level 2 4 1 level 1

There is such logic

level 0: 1 / / \ \ level 1: 2 3 4 5 / level 2: 5

In business, if you need to query the child nodes of node 1, you can use the following syntax in Oracle:

select * from sr_menu start with id = 1 connect by prior id = parent; ID PARENT TITLE ---------- ---------- -------------------------------------------------- 1 level 0 2 1 level 1 3 1 level 1 5 3 level 2 4 1 level 1

When querying, the id=1 (decorated with prior ity) of the previous layer will be compared with the current parent to query the qualified data of the second layer:

2 1 level 1 3 1 level 1 4 1 level 1

Continue recursion later, use the id=2/3/4 of the upper layer to match the following data, and get from id=3:

5 3 level 2

PostgreSQL hierarchical query transformation

Oracle hierarchical query is actually a recursive query method, which recurses out the next layer with the results of the first layer query. In Postgresql, you can use the WITH RECURSIVE syntax to achieve the same function.

The common WITH clause can realize the function of CTE. In addition, the RECURSIVE keyword can further reference its own output in WITH to realize recursion. For example, for the above SQL rewriting, the same business logic can be realized:

WITH RECURSIVE a AS ( SELECT id, parent, title FROM sr_menu WHERE id = 1 UNION ALL SELECT d.id, d.parent, d.title FROM sr_menu d JOIN a ON a.id = d.parent ) SELECT id, parent, title FROM a; id | parent | title ----+--------+--------- 1 | | level 0 2 | 1 | level 1 3 | 1 | level 1 4 | 1 | level 1 5 | 3 | level 2

The first corresponding START WITH statement using UNION ALL in WITH is generally a query condition WITH a fixed result set.

The second table of UNION ALL, join... A.id, indicates that the query results of the current with clause are connected. In this way, recursion is repeated until all data queries are completed.

The execution process can also be seen from the recursion depth:

WITH RECURSIVE a AS ( SELECT id, parent, title, 1::integer recursion_level FROM sr_menu WHERE id = 1 UNION ALL SELECT d.id, d.parent, d.title, a.recursion_level +1 FROM sr_menu d JOIN a ON a.id = d.parent ) SELECT * FROM a; id | parent | title | recursion_level ----+--------+---------+----------------- 1 | | level 0 | 1 2 | 1 | level 1 | 2 3 | 1 | level 1 | 2 4 | 1 | level 1 | 2 5 | 3 | level 2 | 3

About WITH RECURSIVE

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t;

Execution process of recursive WITH:

1. Calculate non recursive items (fixed query part in UNION ALL, such as VALUES(1) above) and put the results in temporary table A
2. If the temporary table is not empty, repeat the following steps:
   1. Calculate the recursive item (the recursive part in UNION ALL) and use temporary table A as A recursive self reference table. Record the query results to temporary table B
   2. Overwrite A with B's database and empty B

6 September 2021, 13:54 | Views: 9485