Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Reference/ Functions and Operators/ Hierarchical Recursion Query Functions
Updated on 2025-05-29 GMT+08:00
View PDF
Share

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, and 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. If the column content is the same as the separator, the function can run properly and no error is reported.

Return type: TEXT

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
-- 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 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. Only columns of the following types are supported: INT8, INT1, INT2, OID, INT4, BOOL, CHAR, NAME, FLOAT4, FLOAT8, ABSTIME, RELTIME, DATE, CASH, TIME, TIMESTAMP, TIMESTAMPTZ, SMALLDATETIME, UUID, INTERVAL, TIMETZ, INT2VECTOR, CLOB, NVARCHAR2, VARCHAR, TEXT, VECTOR, BPCHAR, RAW, BYTEA, NUMERIC, XID, CID, and TID. The whitelist restriction cannot be bypassed by forcibly converting the type to the preceding types, for example, connect_by_root(col::text) (where col is not of the type in the whitelist) is not supported.

Return type: data type of the specified column col.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
-- 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 connect_by_table;
DROP TABLE
Parent topic: Functions and Operators