Retrieving a path with SYS_CONNECT_BY_PATH v18

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

Syntax

The signature of the function is:

SYS_CONNECT_BY_PATH (<column>, <delimiter>)

The function takes two arguments:

• column is the name of a column that resides in a table specified in the hierarchical query that's calling the function.
• delimiter is the varchar value that separates each entry in the specified column.

Examples

This example returns a list of employee names and their managers. If the manager has a manager, that name is appended to the result.

edb=# 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)

In the result set:

• The level column displays the number of levels that the query returned.
• The ename column displays the employee name.
• The managers column contains the hierarchical list of managers.

The EDB Postgres Advanced Server implementation of SYS_CONNECT_BY_PATH doesn't support use of:

• SYS_CONNECT_BY_PATH inside CONNECT_BY_PATH
• SYS_CONNECT_BY_PATH inside SYS_CONNECT_BY_PATH