SYS_CONNECT_BY_PATH is a function that works within a hierarchical query to retrieve the column values of a specified column that occur between the current node and the root node.

The function has the following signature:

SYS_CONNECT_BY_PATH (column, delimiter)

The function provides two parameters:

  • column specifies the name of a column that is located within a table. This table is specified in the hierarchical query that calls the function.
  • delimiter specifies the varchar value that separates each entry in the specified column.

The following example returns a list of names of employees and their managers. If a manager reports to a superior manager, the superior manager name is appended to the result:

polardb=# SELECT level, ename , SYS_CONNECT_BY_PATH(ename, '/') managers
      FROM emp
      CONNECT BY PRIOR empno = mgr
      START WITH mgr IS NULL
      ORDER BY level, ename, managers;
 level | ename  |        managers
-------+--------+-------------------------
     1 | KING   | /KING
     2 | BLAKE  | /KING/BLAKE
     2 | CLARK  | /KING/CLARK
     2 | JONES  | /KING/JONES
     3 | ALLEN  | /KING/BLAKE/ALLEN
     3 | FORD   | /KING/JONES/FORD
     3 | JAMES  | /KING/BLAKE/JAMES
     3 | MARTIN | /KING/BLAKE/MARTIN
     3 | MILLER | /KING/CLARK/MILLER
     3 | SCOTT  | /KING/JONES/SCOTT
     3 | TURNER | /KING/BLAKE/TURNER
     3 | WARD   | /KING/BLAKE/WARD
     4 | ADAMS  | /KING/JONES/SCOTT/ADAMS
     4 | SMITH  | /KING/JONES/FORD/SMITH
(14 rows)

Where:

  • The level column displays the number of levels that the query returns.
  • The ename column displays the employee names.
  • The managers column displays the hierarchical list of managers.

The implementation of SYS_CONNECT_BY_PATH used in PolarDB for PostgreSQL(Compatible with Oracle) does not support use of:

  • SYS_CONNECT_BY_PATH inside CONNECT_BY_PATH
  • SYS_CONNECT_BY_PATH inside SYS_CONNECT_BY_PATH