PolarDB:Hierarchical query pseudocolumns

The hierarchical query pseudocolumns are valid only in hierarchical queries. To define a hierarchical relationship in a query, you must use the CONNECT BY clause. The hierarchical query pseudocolumns are:

• CONNECT_BY_ISCYCLE

• CONNECT_BY_ISLEAF

• LEVEL

CONNECT_BY_ISCYCLE

The value in the CONNECT_BY_ISCYCLE pseudocolumn indicates whether a row is the starting position of a loop in the hierarchy.

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current node has a child which is also its ancestor. Otherwise it returns 0.

In the following figure, node B1 is the starting position of the loop. It has a child node C2 which is also its ancestor.

NOCYCLE in the CONNECT_BY_ISCYCLE clause." placement="break" id="5b70d6400fgd7" />

NOCYCLE prevents queries from failing due to a CONNECT BY loop.

-- Example

-- CREATE TEST TABLE
CREATE TABLE connect_by_table(id INT, pid INT, name TEXT);
INSERT INTO connect_by_table VALUES(1, NULL, 'A');
INSERT INTO connect_by_table VALUES(101, 1, 'B1');
INSERT INTO connect_by_table VALUES(102, 1, 'B2');
INSERT INTO connect_by_table VALUES(10101, 101, 'C0');
INSERT INTO connect_by_table VALUES(10102, 101, 'C1');
INSERT INTO connect_by_table VALUES(10201, 102, 'C2');
INSERT INTO connect_by_table VALUES(102, 10201, 'CYCLE');

SELECT *, CONNECT_BY_ISCYCLE FROM connect_by_table WHERE CONNECT_BY_ISCYCLE=1 START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = pid;
  id   | pid | name | connect_by_iscycle
-------+-----+------+--------------------
 10201 | 102 | C2   |                  1
(1 row)

The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree. Otherwise it returns 0.

In the following figure, the nodes C0 and C1 are leaf nodes in the hierarchy. CONNECT_BY_ISLEAF returns 1 for C0 and C1, and 0 for the other nodes.

Example SELECT name, CONNECT_BY_ISLEAF FROM connect_by_table START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = pid;" placement="break" id="a86798800ff7t" />

LEVEL

For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on.
 The following figure shows the Level 1, Level 2, and Level 3 of the hierarchy. 
 
  
  
  
  
  
  
 

LEVEL

SELECT id, name, LEVEL FROM connect_by_table START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = pid; id | name | level

Example

SELECT id, name, LEVEL FROM connect_by_table START WITH id = 1 CONNECT BY NOCYCLE PRIOR id = pid;
  id   | name | level
-------+------+-------
     1 | A    |     1
   101 | B1   |     2
 10101 | C0   |     3
 10102 | C1   |     3
   102 | B2   |     2
 10201 | C2   |     3
(6 rows)