Seções iMasters
PostgreSQL

Queries recursivas em PostgreSQL

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);
Comente também

24 Comentários

Rafael Almeida

Achei legal esse recurso mas já testou para verificar se tem algum ganho ou perda de desempenho?

Obrigado e abraço.

Beto Lima

André me tire uma dúvida por favor.
Este recursividade nao seria o mesmo que se usássemos uma função em pl?
digo, porque dentro da recursividade temos um select qualquer concorda?
e numa pl também podemos ter um select qualquer, recebendo parametros ou não.
Não estou falando de performance mas somente para entedimento do real propósito de se usar a recursividade. Achei interessante. A impressão que tive de início é que o uso seria parecido como chamar uma pl, mas sem precisarmos compila-lá. Estou certo?

    André Fernandes

    Recusividade é um recurso comum em programação onde se faz possível definir poucos passos e repetir os mesmos para realizar uma tarefa.
    No exemplo que mencionei temos uma representação de funcionários, sendo que estes podem ter gerentes, que também são funcionários. Se for uma empresa com muitos níveis hierárquicos, esses gerentes poderão ter gerentes acima deles também, e assim por diante, até chegar ao presidente da empresa (que seria o superior de todos os demais neste caso).
    Como não sabemos de antemão quantos níveis teríamos, um SQL sem recursão seria impossível, a não ser que fizéssemos um código (poderia ser em PL/PgSQL, por exemplo) que tivesse uma instrução de loop e dentro deste loop um select buscando o nível seguinte. Dessa forma traríamos todos os níveis necessários. Essa seria a ligação com chamar uma PL., mas não sei se era isso que tinhas imaginado.
    Outro exemplo para reforçar essa idéia, é a de seres vivos em uma cadeia alimentar, um ser vivo é alimento para outro ser vivo, mas este também pode ser alimento para um terceiro ser vivo, e assim por diante, até que cheguemos a um ser vivo que está no topo da cadeia alimentar. Neste caso temos uma recursividade, que poderia ser implementada por meio do recurso mostrado.
    Ficou mais fácil de entender?

Beto Lima

obrigado André, mas sinceramente não ficou mais facil do que antes, claro que entendi a idéia do ser vivo e hierarquia de funcionarios. Mas na prática acredito que o ideal seria ter 2 exemplos, um da forma que já estamos acostumados e a outra com a recursividade. Mas aí cabe a você decidir. Grato mesmo pela colaboração.
Abs…

Beto Lima

ficou sim, muito obrigado mesmo….

Darlan Martins

estou com uma duvida

Darlan Martins

tenho uma tablea

CREATE TABLE apoio
(
seq_apoio integer NOT NULL,
sig_apoio character varying(300),
des_apoio character varying(300),
seq_apoio_pai integer,
dat_alteracao date,
sit_cancelado character(1),
CONSTRAINT pk_apoio PRIMARY KEY (seq_apoio)
)

Darlan Martins

/* Data for the `public.apoio` table (Records 1 – 36) */

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (1, ‘ESTADO_CIVIL’, ‘Estado Civil’, NULL, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (2, ‘ESCOLARIDADE’, ‘Escolaridade’, NULL, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (3, ‘SITUACAO_MEMBRO’, ‘Situação do Membro’, NULL, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (4, ‘TIPO_ACEITACAO’, ‘Tipo de Aceitação’, NULL, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (5, ‘TIPO_MEMBRO’, ‘Tipo de Membro’, NULL, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (6, NULL, ‘Solteiro(a)’, 1, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (7, NULL, ‘Casado(a)’, 1, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (8, NULL, ‘Viuvo(a)’, 1, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (9, NULL, ‘Divorciado(a)’, 1, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (10, NULL, ‘Não Alfabetizado’, 2, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (11, NULL, ‘Primario’, 2, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (12, NULL, ‘Ensino Fundamental’, 2, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (13, NULL, ‘Ensino Médio’, 2, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (14, NULL, ‘Superior Completo’, 2, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (15, NULL, ‘Superior Incompleto’, 2, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (16, NULL, ‘Batismo’, 4, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (17, NULL, ‘Transferência’, 4, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (18, NULL, ‘Aclamação’, 4, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (19, NULL, ‘Congregado’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (20, NULL, ‘Membro’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (21, NULL, ‘Diacono(a)’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (22, NULL, ‘Diaconisa’, 5, NULL, ‘S’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (23, NULL, ‘Presbítero’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (24, NULL, ‘Evangelista’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (25, NULL, ‘Pastor(a)’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (26, NULL, ‘Auxiliar’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (27, NULL, ‘Cooperador’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (28, NULL, ‘Missionario(a)’, 5, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (29, NULL, ‘Administrador’, 5, NULL, ‘S’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (30, NULL, ‘Maestro’, 5, NULL, ‘S’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (31, NULL, ‘Ativo’, 3, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (32, NULL, ‘Desviado’, 3, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (33, NULL, ‘Falecido’, 3, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (34, NULL, ‘Mudou-se’, 3, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (35, NULL, ‘Excluido’, 3, NULL, ‘N’);

INSERT INTO “public”.”apoio” (“seq_apoio”, “sig_apoio”, “des_apoio”, “seq_apoio_pai”, “dat_alteracao”, “sit_cancelado”)
VALUES (36, NULL, ‘Disciplina’, 3, NULL, ‘N’);

    Darlan Martins

    Criar um script para que ele monte uma estrutura hierarquica EX:

    Pai 1
    Filho1
    Filho1
    FIlho1
    Pai 2
    Filho2
    Filho2
    Filho2

    dai por diante

Darlan Martins

Preciso Criar com essa estrutura uma querie recursiva Ex:

Pai 1
Filhos1
Filhos1
Filhos1
Pai 2
Filhos2
Filhos2
Filhos2

    Walquirio Saraiva Rocha

    não precisa de mãe somente o pai e filho na tabela se você fizer um select vc vai ver que seq_apoio = 1 e seq_apoio pai = null então esse 1 é pai e quando tiver o seq_apoio = 2 e seq_apoio_pai = 2 então o 2 não é pai e sim filho logo não precisa de mãe… vou montar o select e passar aqui no forum

Kleber Calegário

Excelente artigo André, nessa nova versão foi implementado mais alguma nova ferramenta?

Abraços.

Kleber

Evandro Rispar

Ótimo Artigo… Gostaria de saber se existe a possibilidade fazer a recursividade em níveis a cima do id, ex: Passo o id do filho e quero que retorne o Pai, e o Filho desse id se houver;

Motta

Faltou apenas :

O Exemplo com o resultado da query

Informar se existe como saber em que nível(level) estamos, importante para montar visualmente uma hierarquia.

Lucas Moraes

Parabéns! Muito bom artigo.
Como você é DBA, poderia criar um artigo sobre banco de dados temporal com PostgreSQL, seria bacana e a comunidade agradeceria. :)

Jackson Jorge

Caramba… Não acredito que é tão simples assim com PG, precisei fazer algo parecido no MySql e só consegui com Stored Procedures e tabela temporária….

Infelizmente devido a falta de tempo não estou conseguindo estudar PG e migrar para ele, mas já está ma minha lista de mudanças à fazer.

Muito bom o artigo, parabéns!

Belchior Palma

ótimo artigo!

teste

ótimo artigo!

Anderson

Me ajudou pra caramba essa function que você criou André, pois como estou usando o postgresql 8.3 não dá para usar o with recursive. Valeu…

Marisergio Alves

André,
Fiquei muito interessado em saber como funciona internamente a recursão em SQL, não somente a sintaxe. Se não for muito incômodo, disponibilise um link de uma boa bibliografia. Gostaria também de saber como é que acontece a parada da recursão em SQL. Obrigado e parabéns.

Wagner Santos

Olá, André!
Cara, segui o exemplo e tive o mesmo problema do Darlan. Estou tentando criar uma hierarquia de setores, no mesmo estilo dessa de funcionários do teu exemplo. Quando rodo minha query, a ordenação está ocorrendo pelo nível. No meu caso, tenho um setor que é o principal (sem pai) e todos os outros vão abaixo dele

Ex.:
- Pai
- – Filho 1
- – Filho 2
- – Filho 3
- – - Filho 1.1
- – - Filho 1.2
- – - Filho 2.1

O exemplo está confuso, mas a query traz o pai em primeiro lugar, logo em seguida todos os seus filhos diretos, depois disso os filhos dos filhos e assim por diante. Quer dizer, eles estão ordenados pelo seu nível de “profundidade” na cadeia, e não hierarquicamente. O ideal seria como no exemplo abaixo.

Ex.:
- Pai
- – Filho 1
- – - Filho 1.1
- – - Filho 1.2
- – Filho 2
- – - Filho 2.1
- – Filho 3

Tens alguma idéia de como posso resolver isso? Procurei informações pela internet mas não encontrei/entendi.

Obrigado pelo artigo! Um abraço!

Alisson

Boa tarde amigo, muito bom seu POST. Não sou DBA, sou um mero programador e estou tendo dificuldade para trazer meus dados. Meu cenário é o seguinte, eu tenho sempre o ULTIMO FILHO, e gostaria de encontrar o PAI FULL deles, para isso, preciso de um SQL Recursivo, certo ??? Tentei seguir seu exemplo, mas ele traz todos os dados da tabela, sendo q gostaria de apenas os filhos, até achar o pai de TAL ITEM. segue abaixo o código:

WITH RECURSIVE arvore (id) AS
(
SELECT cod_item_despesa FROM sisdoc_v2.oog_item_despesa where cod_item_despesa = 15547
UNION ALL
SELECT cod_item_despesa FROM sisdoc_v2.oog_item_despesa INNER JOIN arvore ON cod_item_oog_anterior = arvore.id
)
SELECT cod_item_despesa FROM sisdoc_v2.oog_item_despesa;

Poderia me ajudar e ver aonde estou errando ???

O pai de todos, tem o campo? cod_item_oog_anterior null e seus filhos tem o campo preenchido com a chave primária de seus pais, exemplo:

PAI : PK: 5213 cod_item_oog_anterior: NULL
FILHO: PK: 11339 cod_item_oog_anterior: 5213
FILHO: PK: 13965 cod_item_oog_anterior: 11339
FILHO: PK: 15547 cod_item_oog_anterior: 13965

Obrigado amigo, desde já!

Qual a sua opinião?