Query recursiva no PostgreSQL (estrutura de árvore)

Suponha-se que você possua uma tabela com uma ligação (FK) para si mesma e necessita listar todos os níveis de cada registro, conhecido também como estrutura de árvore. Para obter esse resultado iremos utilizar uma consulta recursiva.

O conceito de query recursiva é montar o resultado por níveis, identificando quem são as ocorrências "pais", depois os "filhos de primeiro nível", em seguida, "os filhos de segundo nível" e assim sucessivamente até chegar ao último nível, quando termina a consulta.

Como exemplo, iremos utilizar um cadastro de grupos de produtos, aonde um determinado produto pode possuir um grupo que contém um ou mais níveis.

Veja o diagrama a seguir:



Script para gerar e inserir dados na tabela "grupo":

CREATE TABLE grupo
(
  id INTEGER NOT NULL,
  descricao CHARACTER VARYING(50),
  id_pai INTEGER,
  CONSTRAINT pk_grupo PRIMARY KEY (id),
  CONSTRAINT fk_grupo_pai FOREIGN KEY (id_pai) REFERENCES grupo (id)
);

INSERT INTO grupo VALUES (1, 'ELETRÔNICO', NULL);
INSERT INTO grupo VALUES (2, 'BRINQUEDO', NULL);
INSERT INTO grupo VALUES (3, 'PAPELARIA', NULL);
INSERT INTO grupo VALUES (4, 'COMPUTADOR', 1);
INSERT INTO grupo VALUES (5, 'DVD PLAYER', 1);
INSERT INTO grupo VALUES (6, 'IMPRESSORA', 1);
INSERT INTO grupo VALUES (7, 'JATO DE TINTA', 6);
INSERT INTO grupo VALUES (8, 'LASER', 6);
INSERT INTO grupo VALUES (9, 'BONECA', 2);
INSERT INTO grupo VALUES (10, 'CARRINHO', 2);
INSERT INTO grupo VALUES (11, 'CONTROLE REMOTO', 10);
INSERT INTO grupo VALUES (12, 'MINIATURA', 10);
INSERT INTO grupo VALUES (13, 'CADERNO', 3);
INSERT INTO grupo VALUES (14, 'CANETA', 3);
INSERT INTO grupo VALUES (15, 'LÁPIS', 3);

A consulta a seguir, lista todos os grupos com a descrição do nível acima (pai):

SELECT
  grupo.id,
  COALESCE(sub.descricao || ' > ', '') || grupo.descricao AS desc
FROM
  grupo
LEFT JOIN
  grupo sub ON grupo.id_pai = sub.id
ORDER BY
  grupo.id;

Resultado:



Até aqui tudo bem, mas imagine que você necessite retornar em uma consulta todos os níveis dos registros, por exemplo:

Para o registro de código 11 (CONTROLE REMOTO), ao invés de mostrar somente o pai (CARRINHO), listar também todos os níveis acima a qual o grupo pertence (BRINQUEDO > CARRINHO). Isso é possível através de uma query recursiva utilizando a expressão WITH RECURSIVE, presente a partir da versão 8.4 do PostgreSQL.

WITH RECURSIVE arvore AS
(
  SELECT
    grupo.id,
    grupo.descricao,
    grupo.id_pai,
    CAST(grupo.descricao AS TEXT) AS desc
  FROM
    grupo
  WHERE
    grupo.id_pai IS NULL
  UNION ALL
  SELECT
    grupo.id,
    arvore.descricao,
    grupo.id_pai,
    CAST(arvore.desc || ' > ' || grupo.descricao AS TEXT) AS desc
  FROM
    grupo
  INNER JOIN
    arvore ON grupo.id_pai = arvore.id
)
SELECT
  arvore.id,
  arvore.desc
FROM
  arvore
ORDER BY
  arvore.desc;

A primeira consulta trás todos os registros que não contém um membro pai (grupo.id_pai IS NULL), conhecidos como registros raízes.

Já a outra consulta, será executada a cada passo da recursão, aonde os códigos (id) deverão ser iguais para saber quem é filho de quem (grupo.id_pai = arvore.id).

A parte recursiva foi trabalhada para que tivesse uma união com os dados do passo anterior. Por isso foi usado um INNER JOIN ali dentro. Essa segunda query sempre terá referência à própria recursão.

E por fim, é executado uma consulta para listar os dados necessários da árvore.

Resultado: