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, TEXT, INT1, INT2, INT4, INT8, FLOAT4, FLOAT8, or NUMERIC type are supported. The separator parameter indicates the separator between path nodes. Currently, the col input of the function does not support expression input.

Return type: text

Example:

     
      
        
        
          -- Create a table and insert data.
gaussdb=# CREATE TABLE connect_by_table(id int,pid int,name text);
CREATE TABLE
gaussdb=# INSERT INTO connect_by_table VALUES(1,0,'a'),(2,1,'b'),(3,2,'c'),(4,1,'d');
INSERT 0 4
-- Query.
gaussdb=# SELECT *, sys_connect_by_path(name, '-') FROM connect_by_table START WITH id = 1 CONNECT BY prior id = pid ORDER BY pid, id;
 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)
-- Restore the environment.
gaussdb=# DROP TABLE IF EXISTS connect_by_table;
DROP TABLE

         

       

     
    

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:

     
      
        
        
          -- Create a table and insert data.
gaussdb=# CREATE TABLE connect_by_table(id int,pid int,name text);
CREATE TABLE
gaussdb=# INSERT INTO connect_by_table VALUES(1,0,'a'),(2,1,'b'),(3,2,'c'),(4,1,'d');
INSERT 0 4
-- Query.
gaussdb=# SELECT *, connect_by_root(name) FROM connect_by_table START WITH id = 1 CONNECT BY prior id = pid ORDER BY pid, id;
 id | pid | name | connect_by_root
----+-----+------+-----------------
  1 |   0 | a    | a
  2 |   1 | b    | a
  4 |   1 | d    | a
  3 |   2 | c    | a
(4 rows)
-- Restore the environment.
gaussdb=# drop table if exists connect_by_table;
DROP TABLE

         

       

     
    