I need to insert data into new table person_department using recursion.
My tables for now:
person
id group_id
800 10
805 21
department
id name group_id parent_id
1 ABC 10 5
2 TY 11 5
5 OOO null 9
6 BN 21 9
9 AA null 10
10 WEB null null
So new table should be filled like:
person_id department_id
800 1
800 5
800 9
800 10
805 6
805 9
805 10
For each person, I need to set the corresponding department and all its parents.
I tried this:
WITH RECURSIVE department_hierarchy AS (
SELECT d.id AS department_id, d.parent_id
FROM department d
JOIN person p ON d.group_id = p.group_id
UNION ALL
SELECT d.id AS department_id, d.parent_id
FROM department d
JOIN department_hierarchy dh ON d.id = dh.parent_id
)
INSERT INTO person_department (person_id, department_id)
SELECT p.id AS person_id, dh.department_id
FROM person p
JOIN department_hierarchy dh ON dh.parent_id IS NULL OR dh.department_id IN (
SELECT department_id FROM department_hierarchy
);
But it is not working correctly.
WITH RECURSIVE department_hierarchy AS (
SELECT p.id AS person_id, d.id AS department_id
FROM person p
JOIN department d USING (group_id)
UNION ALL
SELECT dh.person_id, d.parent_id
FROM department_hierarchy dh
JOIN department d ON d.id = dh.department_id
WHERE d.parent_id IS NOT NULL -- break condition
)
INSERT INTO person_department (person_id, department_id)
SELECT *
FROM department_hierarchy
ORDER BY 1,2 -- optional?
RETURNING *; -- optional