PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Generate rows for an INSERT using recursion

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.

Tags:

2

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search