Запрос Postgresql для получения n-уровневого отношения родитель-потомок, хранящегося в одной таблице

Question

У меня есть таблица, обозначающая отношения родителей и детей. Отношения могут идти n-уровня глубоко.

Я создал образец таблицы, используя следующий запрос:

CREATE SEQUENCE relations_rel_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
CREATE TABLE relations(
    rel_id bigint DEFAULT nextval('relations_rel_id_seq'::regclass) NOT NULL PRIMARY KEY,
    rel_name text,
    rel_display text,
    rel_parent bigint
);

SQLFiddle

Мне нужно запросить таблицу и отобразить родительско-дочерние отношения иерархически. Я до сих пор не понимаю, как сделать глубокий n-уровневый запрос с помощью SQL-запроса.

Для sqlfiddle, например, ожидаемая иерархия вывода:

rel1
    rel11
        rel111
        rel112
            rel1121
rel2
    rel21
        rel211
        rel212

NB: значение n, в n-level неизвестно

Дизайн БД:

Есть ли лучший способ выразить такое отношение в базе данных для облегчения запроса?

Answer 1

С Postgres вы можете использовать рекурсивное общее табличное выражение:

with recursive rel_tree as (
   select rel_id, rel_name, rel_parent, 1 as level, array[rel_id] as path_info
   from relations 
   where rel_parent is null
   union all
   select c.rel_id, rpad(' ', p.level * 2) || c.rel_name, c.rel_parent, p.level + 1, p.path_info||c.rel_id
   from relations c
     join rel_tree p on c.rel_parent = p.rel_id
)
select rel_id, rel_name
from rel_tree
order by path_info;

SQLFiddle на основе вашего примера: http://sqlfiddle.com/

(Я заменил пробелы для отступа подчеркиванием, так как SQLFiddle не отображает пробелы правильно)