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

Código python para rodar arquivos .sql no postgres

Código python para rodar arquivos .sql no postgres

Nesta semana o Postgres vem me desafiando, e meu pc também, porque por algum motivo o PGADMIN está demorando uma... Leia mais

Converter campos de um banco de dados do tipo json para array com laravel

Converter campos de um banco de dados do tipo json para array com laravel

Hoje boa parte dos banco de dados relacionais podem armazenar campos com valores no formato JSON o que garante maior... Leia mais

Guia de Deploy de Aplicação Laravel com Nginx em uma VPS com Ubuntu e HTTPS em um VPS

Guia de Deploy de Aplicação Laravel com Nginx em uma VPS com Ubuntu e HTTPS em um VPS

Neste post, você aprenderá a fazer o deploy de uma aplicação Laravel em uma VPS rodando Ubuntu com o servidor... Leia mais

Como instalar o Go Language no Ubuntu ?

Como instalar o Go Language no Ubuntu ?

Então resolvi tirar um tempo para conhecer outras linguagens de programação e no momento resolvi iniciar com a GO pois... Leia mais