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:

Comentários

  1. Material muito bom. Sucinto, mas poderoso. Valeu.

    ResponderExcluir
  2. e se eu quiser fazer uma condição, pra vim só o pai a
    partir de uma identificação?

    ResponderExcluir
    Respostas
    1. Você pode colocar essa condição na primeira SELECT do CTE, observe que tem a cláusula que traz todos os registros raiz utilizando "grupo.id_pai IS NULL".

      Excluir
    2. É eu tentei fazer o inverso, por que a ideia seria pegar do filho o pai, mas mesmo assim ele esta listando todos os registros.

      A ideia seria

      pelo id = 67

      67 > 66 > 65 (aqui parava até chegar no pai)

      Excluir
    3. Na primeira SELECT você filtra pelo ID que deseja e no segundo você inverte os campos, algo assim:

      WITH RECURSIVE arvore AS
      (
      SELECT
      grupo.id,
      grupo.descricao,
      grupo.id_pai,
      CAST(grupo.descricao AS TEXT) AS desc
      FROM
      grupo
      WHERE
      grupo.id = 11
      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 = arvore.id_pai
      )
      SELECT
      arvore.id,
      arvore.desc
      FROM
      arvore
      ORDER BY
      arvore.desc;

      Excluir
    4. foi eu tentei isso, mas por incrível a condição não faz diferença...

      Excluir
    5. Eu acabei de executar esse exemplo e trouxe o pai a partir do filho. Cria uma base para teste e execute o script que está no início do artigo (create / insert). Depois rode essa query e veja o resultado.

      Excluir
    6. Descobri amigo, falta de atenção minha, espero que servi pra mais alguém, logo a query abaixo eu referenciei a tabela e não a arvore, desculpa e obrigado! passei o dia nisso kkk minha noite vai ser longa ;)

      Excluir

Postar um comentário