Queries recursivas em PostgreSQL

André Fernandes
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);

André Fernandes

24 comentários Comente também

  1. 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?

    1. 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?

  2. 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…

  3. 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)
    )

  4. /* 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’);

    1. 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

  5. Ó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;

  6. 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.

  7. 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. :)

  8. 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!

  9. 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.

  10. 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!

  11. 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á!

Dê Sua Opinião

O seu endereço de email não será publicado Campos obrigatórios são marcados *

Este projeto é mantido e patrocinado pelas empresas: