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