Сгенерируйте строки для INSERT, используя рекурсию

Мне нужно вставить данные в новую таблицу person_department с помощью рекурсии.

Мои таблицы на данный момент:

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

Поэтому новая таблица должна быть заполнена следующим образом:

person_id    department_id
800                 1
800                 5
800                 9
800                10
805                 6
805                 9
805                10

Для каждого человека мне нужно задать соответствующий отдел и всех его родителей.

Я попробовал это:

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
);

Но он работает неправильно.

Рекурсивный cte состоит из двух SELECT — одного с начальной строкой (привязкой) и рекурсивного для перехода вперед.

— 20.08.2024 15:41

sql database postgresql recursive-query

20.08.2024 15:38

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

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

20.08.2024 17:54

Ответ принят как подходящий

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

рабочий пример

20.08.2024 17:55