Hierarchical Recursion Query Functions

The following functions can be used in a hierarchical recursion query statement to return information about the connection path.

sys_connect_by_path(col, separator)

Description: Returns the connection path from the root node to the current row. This function applies only to hierarchical recursion queries.

The col parameter indicates the name of the column displayed in the path. Only columns of the CHAR, VARCHAR, NVARCHAR2, and TEXT types are supported. The separator parameter indicates the separator between path nodes.

Return type: text

Example:

     
          openGauss=# select *, sys_connect_by_path(name, '-') from connect_table start with id = 1 connect by prior id = pid;
 id | pid | name | sys_connect_by_path
----+-----+------+---------------------
  1 |   0 | a    | -a
  2 |   1 | b    | -a-b
  4 |   1 | d    | -a-d
  3 |   2 | c    | -a-b-c
(4 rows)

connect_by_root(col)

Description: Returns the value of a column in the top-most parent row of the current row. This function applies only to hierarchical recursion queries.

The col parameter indicates the name of an output column.

Return type: data type of the specified column col.

Example:

     
          openGauss=# select *, connect_by_root(name) from connect_table start with id = 1 connect by prior id = pid;
 id | pid | name | connect_by_root
----+-----+------+-----------------
  1 |   0 | a    | a
  2 |   1 | b    | a
  4 |   1 | d    | a
  3 |   2 | c    | a
(4 rows)