PolarDB:Hierarchical query pseudocolumns

Tree node terminology

The following terms describe node roles in a hierarchical query. Later sections use these terms to explain each pseudocolumn's behavior.

Sample table

All examples in this topic use the following 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');

The tree structure is:

A  (level 1)
+-- B1 (level 2)
|   +-- C0 (level 3, leaf)
|   +-- C1 (level 3, leaf)
+-- B2 (level 2)
    +-- C2 (level 3) -- cycles back to B2 (id=102)

CONNECT_BY_ISCYCLE

CONNECT_BY_ISCYCLE returns 1 if the current row has a child that is also its ancestor — meaning the current row is the starting point of a cycle. Otherwise it returns 0.

To use CONNECT_BY_ISCYCLE, include NOCYCLE in the CONNECT BY clause. Without NOCYCLE, a query fails when it encounters a cycle in the data.

Example: Find all rows that are the starting point of a cycle.

SELECT *, CONNECT_BY_ISCYCLE
FROM connect_by_table
WHERE CONNECT_BY_ISCYCLE = 1
START WITH id = 1
CONNECT BY NOCYCLE PRIOR id = pid;

Output:

  id   | pid | name | connect_by_iscycle
-------+-----+------+--------------------
 10201 | 102 | C2   |                  1
(1 row)

Row C2 (id=10201) has a child row with id=102, which is also C2's ancestor B2. This makes C2 the cycle start, so CONNECT_BY_ISCYCLE returns 1.

CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF returns 1 if the current row has no children in the hierarchy. Otherwise it returns 0.

Example: List all rows and whether each is a leaf node.

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

Output:

 name | connect_by_isleaf
------+-------------------
 A    |                 0
 B1   |                 0
 C0   |                 1
 C1   |                 1
 B2   |                 0
 C2   |                 1
(6 rows)

C0 and C1 are leaf nodes. C2 returns 1 because its child causes a cycle and traversal stops under NOCYCLE, so C2 has no traversable children. A, B1, and B2 return 0 because each has at least one child.

LEVEL

LEVEL returns the depth of each row in the hierarchy: 1 for root rows, 2 for their direct children, 3 for the next level, and so on.

Example: Return each row along with its depth level.

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

Output:

  id   | name | level
-------+------+-------
     1 | A    |     1
   101 | B1   |     2
 10101 | C0   |     3
 10102 | C1   |     3
   102 | B2   |     2
 10201 | C2   |     3
(6 rows)

A is the root row, so LEVEL returns 1. B1 and B2 are direct children of A, so LEVEL returns 2. C0, C1, and C2 are one level deeper, so LEVEL returns 3.

Usage notes

• All three pseudocolumns are valid only inside a hierarchical query that includes CONNECT BY.

• CONNECT_BY_ISCYCLE requires NOCYCLE in the CONNECT BY clause. Without NOCYCLE, the query fails if a cycle exists in the data.