Olá! Hoje falarei de um dos recursos de que mais gosto em postgreSQL:
recursividade. É um recurso recente, foi inserido na versão 8.4, por
isso vejo muitas pessoas que ainda não sabem usar ou mesmo nem sabiam da
existência.
Vamos à estrutura de uma query recursiva. Primeiro passo para
construir uma query dessas é descobrir qual a primeira busca que ela
fará, o passo inicial. Por exemplo, se tiveres uma tabela de
funcionários (o script para criar a tabela está no final do artigo) que
pode ter nela uma chave estrangeira para ela mesma indicando o seu
superior, por onde começaríamos nossa busca? Digamos que desejamos todos
os funcionários que trabalhem para o funcionário de id igual a 3, o
primeiro passo seria selecionar todos logo abaixo dele.
Então, nesse caso, teríamos o select a seguir:
select id, nome from funcionario where superior_id =3
Até aqui não temos nada novo, certo? Agora precisamos criar um select
que será executado a cada passo da recursão. No nosso caso, queremos os
funcionários abaixo dele. Assim, o que precisamos é que o superior_id
desse funcionário seja igual ao de quem é funcionário dele ou dele
diretamente, certo? Façamos um esboço disso:
select id, nome from funcionario where superior_id = …. .id
E agora vem a “mágica”, precisamos unir ambas as partes (UNION ALL) e
usar comandos sql novos do postgresql, o “with recursive”:
WITH RECURSIVE arvore_funcionarios (id, nome) AS
(
SELECT id, nome FROM funcionario WHERE superior_id =3
UNION ALL
SELECT funcionario.id, funcionario.nome FROM funcionario INNER JOIN arvore_funcionarios ON funcionario.superior_id = arvore_funcionarios.id
)
Somente um aviso aqui. 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.
Até aqui temos a estrutura da árvore de funcionários abaixo do
funcionário 3. No primeiro passo, serão trazidos todos os funcionários
logo abaixo dele, logo após, para cada um deles será feito o select que
usa a própria estrutura que chamei de arvore_funcionario (uma recursão
propriamente dita).
Mas falta uma coisa. Agora falta selecionar os registros dessa árvore de funcionários. Isso é simples, veja a seguir:
with recursive arvore_funcionarios (id, nome) as
(
select id, nome from funcionario where superior_id =3
UNION ALL
select funcionario.id, funcionario.nome from funcionario INNER JOIN arvore_funcionarios ON funcionario.superior_id = arvore_funcionarios.id
)
select id, nome from arvore_funcionarios;
E pronto! Temos todos os funcionários abaixo do funcionário 3.
Com pequenos arranjos, podemos obter o custo de pessoal (soma dos
salários) sob uma gerência. Vamos novamente usar como gerente o
funcionario de id igual a 3. Nesse caso, queremos todos abaixo do gerente
e somar seus salários (não excluiremos o salário do gerente porque esse
salário também faz parte do custo de pessoal). O que teríamos de fazer
nesse caso?
Primeiro pegamos o gerente em si:
select id, nome, salario from funcionario where id =3
A parte recursiva seria semelhante à anterior, apenas adicionando a coluna salario, assim não vou repetir essa parte aqui.
Vamos à query final então:
with recursive arvore_funcionarios (id, nome, salario) as
(
select id, nome, salario from funcionario where id =3
UNION ALL
select funcionario.id, funcionario.nome, funcionario.salario from funcionario INNER JOIN arvore_funcionarios ON funcionario.superior_id = arvore_funcionarios.id
)
select sum(salario) as salario_total, count(id) as total_funcionarios from arvore_funcionarios;
Nesse caso, troxe inclusive o total de funcionários abaixo desse gerente.
Muitos outros usos existem para queries recursivas, aqui dei apenas
uma introdução. Contudo, se ficou alguma dúvida ou se precisarem de ajuda
em um caso específico, deixem um comentário.
Abraços!
————————————————————————
Script de criação da tabela funcionário:
create table funcionario
(id bigint,
nome varchar(80),
salario numeric(18,3),
superior_id bigint);
ALTER TABLE funcionario ADD CONSTRAINT pk_funcionario PRIMARY KEY (id);
51 Comentários
Qual a sua opinião?