PauloRB.dev

A função WITH RECURSIVE no PostgreSQL

Por Paulo RB em 11/04/2024

A função WITH RECURSIVE no PostgreSQL é uma ferramenta poderosa para a execução de consultas recursivas em bancos de dados. Este recurso é especialmente útil quando se trabalha com dados hierárquicos ou relacionais, como árvores de categorias, estruturas organizacionais ou grafos. Neste artigo, vamos explorar como a função WITH RECURSIVE funciona e como você pode utilizá-la para resolver problemas complexos de maneira eficiente.

A cláusula WITH é usada no PostgreSQL para criar uma expressão de tabela comum (Common Table Expression – CTE), que é uma subconsulta temporária que retorna um conjunto de resultados temporário. WITH RECURSIVE expande essa capacidade permitindo que essas subconsultas referenciem a si mesmas, criando uma CTE recursiva.

Uma CTE recursiva é composta de duas partes: a parte não recursiva e a parte recursiva. A parte não recursiva é a base da recursão, fornecendo o caso base. A parte recursiva é onde a CTE chama a si mesma, permitindo a navegação através de níveis hierárquicos.

A sintaxe básica de uma consulta WITH RECURSIVE é a seguinte:

WITH RECURSIVE nome_temporario AS (
    -- Parte não recursiva
    SELECT ...
    UNION ALL
    -- Parte recursiva
    SELECT ... FROM nome_temporario WHERE ...
)
SELECT * FROM nome_temporario;

Vamos a um exemplo prático:

Primeiro, vamos definir a estrutura da tabela funcionarios. Esta tabela incluirá colunas para o id do funcionário, seu nome, e o id do seu gerente (id_gerente), onde id_gerente é uma referência ao id de outro funcionário que atua como seu gerente.

CREATE TABLE funcionarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    id_gerente INTEGER,
    FOREIGN KEY (id_gerente) REFERENCES funcionarios(id)
);

Em seguida, inseriremos alguns dados de exemplo para representar uma estrutura organizacional simples. Incluiremos um gerente geral no topo e diversos níveis de gerentes e funcionários abaixo dele.

INSERT INTO funcionarios (nome, id_gerente) VALUES
('Gerente Geral', NULL),  -- id 1
('Gerente A', 1),  -- id 2
('Gerente B', 1),  -- id 3
('Funcionário A1', 2),  -- id 4
('Funcionário A2', 2),  -- id 5
('Funcionário B1', 3),  -- id 6
('Funcionário B2', 3),  -- id 7
('Subgerente A1', 2),  -- id 8
('Funcionário A1.1', 8);  -- id 9

Agora que temos nossa tabela e dados, vamos escrever uma consulta usando WITH RECURSIVE para listar todos os subordinados do “Gerente Geral”, diretamente ou através de outros gerentes.

WITH RECURSIVE subordinados AS (
    SELECT id, nome, id_gerente
    FROM funcionarios
    WHERE id_gerente IS NULL  -- Caso base: o gerente geral não tem id_gerente
    UNION ALL
    SELECT f.id, f.nome, f.id_gerente
    FROM funcionarios f
    INNER JOIN subordinados s ON f.id_gerente = s.id  -- Parte recursiva
)
SELECT * FROM subordinados WHERE id != 1;  -- Exclui o gerente geral da lista final

Esta consulta inicia com o “Gerente Geral” como caso base, que é o único funcionário sem id_gerente (indicando que não tem superior), e recursivamente inclui todos os funcionários que estão abaixo dele na hierarquia, utilizando a união (UNION ALL) entre a parte não recursiva e a parte recursiva da consulta. O resultado final exclui o “Gerente Geral” da lista para focar nos subordinados.

Assim temos o resultado:

Espero ter ajudo você a entender melhor a função recursive e seus uso em projetos de software.