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;

<span style="font-family: Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif; font-size: 13px; line-height: 19px;">Nesse caso, troxe inclusive o total de funcionários abaixo desse gerente.</span>

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);