Oracle Recursive joins with CONNECT BY PRIOR
Oracle uses special syntax to perform recursive joins (or hierachical queries): START WITH ... CONNECT BY PRIOR
It must be rewritten as a recursive CTE with PostgreSQL.
A generated-name CTE must be composed by:
- a starting set
-
UNION ALL
operator - a recursive set with an stop condition
Example dataset
DROP TABLE employees;
CREATE TABLE employees (
id int PRIMARY KEY,
name varchar(255),
job varchar(255),
manager int
);
ALTER TABLE employees ADD CONSTRAINT manager_fk FOREIGN KEY (manager) REFERENCES employees(id);
INSERT ALL
INTO employees VALUES (1, 'top0', 'boss', NULL)
INTO employees VALUES (2, 'mgr0', 'manager', 1)
INTO employees VALUES (3, 'emp0', 'employee', 2)
INTO employees VALUES (4, 'emp1', 'employee', 2)
INTO employees VALUES (5, 'emp2', 'employee', 2)
INTO employees VALUES (6, 'mgr1', 'manager', 1)
INTO employees VALUES (7, 'emp3', 'employee', 6)
INTO employees VALUES (10, 'top1', 'boss', NULL)
INTO employees VALUES (11, 'mgr3', 'manager', 10)
INTO employees VALUES (12, 'emp4', 'employee', 11)
SELECT NULL FROM DUAL;
Simple case
SELECT id, name, job, manager
FROM employees
START WITH manager IS NULL AND name = 'top0'
CONNECT BY PRIOR id = manager
ORDER BY id
Becomes:
WITH RECURSIVE employees_hierarchy(id, name, job, manager) AS (
SELECT id, name, job, manager
FROM employees
WHERE manager IS NULL AND name = 'top0'
UNION ALL
SELECT id, name, job, manager
FROM employees
JOIN employees_hierarchy AS "prior" ON employees.manager = "prior".id
)
SELECT id, name, job, manager
FROM employees_hierarchy AS employees
ORDER BY id;
LEVEL
SELECT id, name, job, manager, level
FROM employees
START WITH name = 'top0'
CONNECT BY PRIOR id = manager;
Becomes:
WITH RECURSIVE employees_hierarchy(id, ename, job, manager) AS (
SELECT id, name, job, manager, 1 AS level
FROM employees
WHERE manager IS NULL AND ename = 'top0'
UNION ALL
SELECT recursion.id, recursion.name, recursion.job, recursion.manager, "prior".level + 1 AS level
FROM employees AS recursion
JOIN employees_hierarchy AS "prior" ON "recursion".manager = "prior".id
)
SELECT id, name, job, manager, level
FROM employees_hierarchy AS employees
ORDER BY id;
Unhandled features:
Raise an error for:
- NOCYCLE
- CONNECT_BY_IS_CYCLE
- ORDER SIBLINGS BY
- CONNECT_BY_ROOT in SELECT list
- CONNECT_BY_ISLEAF in SELECT list
References
Edited by Étienne BERSAC