How to convert this query to its equivalent postgresql hierarchical query? How to replace CONNECT_BY_ISLEAF function in postgresql?
SELECT emp_id,mgr_id,name,SYS_CONNECT_BY_PATH (name,'/') PATH ,CONNECT_BY_ISLEAF ISLEAF
FROM employee
START WITH (emp_id = 345)
CONNECT BY NOCYCLE (PRIOR emp_id = mgr_id)
This is the equivalent in Oracle using a Recursive Sub-Query factoring clause (a.k.a. Common Table Expression). It should map (maybe with some changes in syntax) to PostgreSQL:
WITH cte ( emp_id, mgr_id, name, path, leaf ) AS (
SELECT emp_id,
mgr_id,
name,
'/' || name,
CASE WHEN EXISTS( SELECT 1 FROM employee m WHERE m.mgr_id = e.emp_id )
THEN 0 ELSE 1 END
FROM employee e
WHERE emp_id = 345
UNION ALL
SELECT e.emp_id,
e.mgr_id,
e.name,
c.path || '/' || e.name,
CASE WHEN EXISTS( SELECT 1 FROM employee m WHERE m.mgr_id = e.emp_id )
THEN 0 ELSE 1 END
FROM employee e
INNER JOIN cte c
ON( e.mgr_id = c.emp_id )
)
SELECT * FROM cte;
(Note: this does not account for the NOCYCLE clause of the hierarchical query - if this is necessary then you will need to build in a mechanism to eliminate those joins.)
Recursive queries are done using a recursive common table expression in Postgres.
You can simulate Oracle's level by simply incrementing a value for each iteration and then comparing that in the outer query.
The leaf can be checked using a sub-query - similar to what MT0 did
The nocycle can be done by remembering all rows that have been processed and adding a where condition to the recursive part that stops if an employee was already processed.
By carrying the initial emp_id through all levels, you can also simulate Oracle's connect_by_root
with recursive cte (emp_id, mgr_id, name, path, level, visited, root_id) AS
(
select emp_id,
mgr_id,
name,
'/' || name,
1 as level,
array[emp_id] as visited,
emp_id as root_id
from employee e
where emp_id = 345
union all
select c.emp_id,
c.mgr_id,
c.name,
concat_ws('/', p.path, c.name),
p.level + 1,
p.visited || c.emp_id,
p.root_id
from employee c
join cte p on p.emp_id = c.mgr_id
where c.emp_id <> all(p.visited)
)
SELECT e.*,
not exists (select * from cte p where p.mgr_id = e.emp_id) as is_leaf
FROM cte e;
Online example: http://rextester.com/TSMVV17478
/Manager/Team Lead/Worker and another branch /Manager/Team Lead/Supervisor/Intern neither the Worker or the Intern manage other employees (so would be leaves) but are at different depths (and have the same root).