Generate rows for an INSERT using recursion

Question

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.

A recursive cte consists of two SELECT's - one with a start row (anchor), and a recursive one to step forward. — jarlh
– jarlh, Commented Aug 20, 2024 at 13:41

Erwin Brandstetter · Accepted Answer · 2024-08-20 15:55:15Z

0

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

fiddle

answered Aug 20, 2024 at 15:55

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MatBailie · Accepted Answer · 2024-08-20 15:54:19Z

WITH RECURSIVE
  department_hierarchy AS
(
    -- Get each person's base department, via group id
    SELECT
      p.id    AS person_id, 
      d.id    AS department_id
    FROM
      person       AS p
    INNER JOIN
      department   AS d
        ON d.group_id = p.group_id
    
    UNION ALL
    
    -- Get the parent department for each row found on the previous iteration
    SELECT
      p.person_id, 
      d.parent_id
    FROM
      department_hierarchy   AS p
    INNER JOIN
      department             AS d
        ON d.id = p.department_id
    WHERE
      d.parent_id IS NOT NULL
)
-- Record all iterations of person,dept in the new table
INSERT INTO
  person_department (
    person_id,
    department_id
  )
SELECT
  *
FROM
  department_hierarchy

Collectives™ on Stack Overflow

Generate rows for an INSERT using recursion

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related