PauloRB.dev

Desenvolvedor de Software

A função WITH RECURSIVE no PostgreSQL | PauloRB.dev A função WITH RECURSIVE no PostgreSQL - PauloRB.dev
A função WITH RECURSIVE no PostgreSQL

A função WITH RECURSIVE no PostgreSQL

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:

id nome id_gerente
2 Gerente A 1
3 Gerente B 1
4 Funcionário A1 2
5 Funcionário A2 2
6 Funcionário B1 3
7 Funcionário B2 3
8 Subgerente A1 2
9 Funcionário A1.1 8

 

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

Mais Posts

Como resolver o erro “oauth-private.key does not exist or is not readable” no Laravel

Como resolver o erro “oauth-private.key does not exist or is not readable” no Laravel

O Laravel Passport é uma biblioteca que trabalha com o padrão OAuth2 e fornece uma implementação completa do servidor OAuth2.... Leia mais

Programação Funcional no Elixir: Fundamentos e Exemplos

Programação Funcional no Elixir: Fundamentos e Exemplos

A programação funcional está se tornando cada vez mais popular devido à sua simplicidade e capacidade de lidar com paralelismo... Leia mais

Como iniciar, parar e reiniciar o Apache Web Server no Linux

Como iniciar, parar e reiniciar o Apache Web Server no Linux

Comandos  para controlar o funcionamento do apache em ambientes de desenvolvimento locais: Estes Comandos Servem para ambientes Ubuntu/Debian: Leia mais

Como Usar a Porta 443 para Conexões SSH com o Bitbucket e Contornar Restrições de Rede

Como Usar a Porta 443 para Conexões SSH com o Bitbucket e Contornar Restrições de Rede

Se você já se deparou com a frustrante mensagem de erro “ssh: connect to host bitbucket.org port 22: Network is... Leia mais