Можно ли больше одного UNION в RECURSIVE CTE?

Question

Пример для модели такой:

  WITH RECURSIVE relatives (id, p_id) AS (
    VALUES
      (1, NULL),
      (2, 1),
      (3, 1),
      (6, null),
      (7, 5)
  ), friends (id, p_id) AS (
    VALUES
      (4, 3),
      (5, 4)
  ),
  r (id) AS (SELECT 3 AS id
              UNION
             SELECT CASE WHEN r.id = td.p_id THEN td.id ELSE td.p_id END AS id
               FROM r
               JOIN relatives td ON r.id = td.p_id OR r.id = td.id
              UNION
             SELECT CASE WHEN r.id = td.p_id THEN td.id ELSE td.p_id END AS id
               FROM r
               JOIN friends td ON r.id = td.p_id OR r.id = td.id
  )
SELECT * FROM r WHERE id is not NULL;

Я хочу чтобы в данном случае запрос разрастался не только в сторону поиска родственников (первая рекурсивная часть), но и в сторону поиска друзей (вторая рекурсивная часть), найдя общий компонент связности родственников и друзей. По отдельности обе рекурсивные части рабочие (если закомментить вторую). Но вместе дают ошибку:

[42P19] ERROR: recursive reference to query "r" must not appear within its non-recursive term

Есть ли способ для использования двух UNION в рекурсивном CTE или это строго-настрого запрещено и никаких обходных путей?

Answer 1

Есть ли способ для использования двух UNION в рекурсивном CTE или это строго-настрого запрещено и никаких обходных путей?

В PostgreSQL так можно, но нельзя.

PostgreSQL воспринимает последний подзапрос как recursive part, а все остальные как anchor part. Посему в CTE может быть сколько угодно UNION, но только последнему позволено ссылаться на CTE. Все остальные могут брать данные только из статических объектов (таблицы, представления и пр.) или предыдущих CTE.

В реальности один идет на Восток, другой идет курить. То есть никак не связанные источники связей. Но при этом на каждой итерации должны рассматриваться оба источника (то есть они могут переключаться). Например, у вас есть граф родственников и граф друзей. Но в случае задачи вы на каждой итерации ищете связь и по родственникам, и по друзьям. – Saint

WITH RECURSIVE test_data (id, p_id) AS (
       VALUES
      (1, NULL),
      (2, 1),
      (3, 1),
      (4, 3),
      (5, 4),
      (6, null),
      (7, null)
),
 r (id, p_id, branch) AS (
  SELECT id, p_id, id
  FROM test_data
  WHERE id = 3
  UNION DISTINCT
  SELECT t.id, t.p_id, LEAST(t.id, r.branch)
  FROM test_data t
  JOIN r ON r.id = t.p_id or t.id = r.p_id
 )
SELECT DISTINCT id, p_id
FROM r
ORDER BY id;

id	p_id
1	null
2	1
3	1
4	3
5	4

fiddle

Answer 2

Я нашел такое решение:

  WITH RECURSIVE relatives (id, p_id) AS (
    VALUES
      (1, NULL),
      (2, 1),
      (3, 1),
      (6, null),
      (7, 5)
  ), friends (id, p_id) AS (
    VALUES
      (4, 3),
      (5, 4)
  ),
  r (id) AS (SELECT 3 id
              UNION
             SELECT t.id
               FROM r
               LEFT JOIN relatives td1 ON r.id = td1.p_id OR r.id = td1.id
               LEFT JOIN friends td2 ON r.id = td2.p_id OR r.id = td2.id
               CROSS JOIN LATERAL (
                 VALUES ('Relative', CASE WHEN r.id = td1.p_id THEN td1.id ELSE td1.p_id END)
                      , ('Friend', CASE WHEN r.id = td2.p_id THEN td2.id ELSE td2.p_id END)
                 ) AS t(source, id)
              WHERE t.id is not null)
SELECT * FROM r WHERE id is not NULL;

БЛОГ НА HUSL

Можно ли больше одного UNION в RECURSIVE CTE?

Ответы (2 шт):