Querys Recursivas no Postgres

Querys Recursivas no Postgres

Segue um exemplo prático de como fazer querys recursivas no Postgres, usando genealogia.

O conceito de query recursiva é montar o resultado por níveis, identificando quem são as ocorrências "pais", depois os "os filhos de primeiro nível", em seguida, "os filhos de segundo nível" e por conseguinte, mergulhando até encontrar, o último nível.

O Algorítmo da Query Recursiva é o seguinte:
Passo 1 - Encontrar todas as ocorrências de nível 1, o qual são chamados de ocorrências âncora.
Passo 2 - Baseado nas âncoras, identificar e selecionar todas as ocorrências de nível 2, recursividade
Passo 3 - Já em um processo recursivo, tomando por base o nível 2, identificar e selecionar todas as ocorrências de nível 3 e assim sucessivamente até chegar ao último nível.

O Modo como o Postgres, processa querys recursivas é bem semelhante ao do SGBDR SQL Server.

Obs: Use esse recurso com moderação, pois quanto mais níveis, mais depreciado tende a ser o desempenho desta query.

Segue script para teste na prática de como fazer:

--
-- Nome Artefato/Programa..: querys_recursivas_no_postgres 
-- Empresa.................: 
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) http://emersonhermann.blogspot.com
-- Data Inicio ............: 19/11/2011
-- Data Atual..............: 19/11/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: Postgres
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: Postgres 8.4/9.0/9.1
-- Kernel..................: Nao informado!
-- Finalidade..............: Usar querys recursivas no Postgres 
-- ........................: 
-- OBS.....................: 
--
 
 
/* testando no Postgres 8.4/9.0/9.1 */
 
--DROP TABLE IF EXISTS genealogia;
CREATE TABLE genealogia
(
    id_genealogia integer     PRIMARY KEY
  , nome varchar(25)         NOT NULL
  , id_genealogia_pai integer NULL REFERENCES genealogia(id_genealogia)
 
);
 
--TRUNCATE TABLE genealogia;
 
SELECT * FROM genealogia;
 
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (1,'ABRAÃO',NULL);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (2,'ISAC',1);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (3,'ESAÚ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (4,'JACÓ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (5,'RÚBEN',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (6,'SIMEÃO',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (7,'LEVI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (8,'JUDÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (9,'ISSACAR',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (10,'ZEBULON',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (11,'JOSÉ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (12,'BENJAMIM',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (13,'DÃ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (14,'NAFTALI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (15,'GADE',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (16,'ASER',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (17,'DINÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (18,'PEREZ',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (19,'ZERA',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (20,'ESRON',18);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (21,'ARÃO ',20);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (22,'AMINADABE',21);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (23,'NASSON',22);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (24,'SALMON',23);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (25,'BOAZ',24);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (26,'OBEDE',25);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (27,'JESSÉ',26);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (28,'DAVI',27);
 
SELECT * FROM genealogia;
 
 
--query 1, AUTO RELACIONAMENTO
 
            SELECT g1.nome
                 , g1.id_genealogia
                 , g2.id_genealogia_pai 
              FROM genealogia g1
         LEFT JOIN genealogia g2 
                ON g1.id_genealogia = g2.id_genealogia_pai 
                 ;   
 
--query 2, RECURSIVA 
 
 
WITH RECURSIVE cte_recursiva (id_genealogia,nome,nivel,arvore)
AS
(
    -- 1a PARTE ANCORA
    SELECT id_genealogia
         , nome
         , 1 AS nivel
         , CAST(nome AS VARCHAR(255)) AS arvore
      FROM genealogia
     WHERE id_genealogia_pai IS NULL
    
    UNION ALL
    
    -- 2a PARTE RECURSIVA
    SELECT g.id_genealogia
         , g.nome
         , c.nivel + 1 AS nivel
         , CAST((c.arvore || '/' || g.nome) AS VARCHAR(255)) AS arvore 
      FROM genealogia g
INNER JOIN cte_recursiva c 
        ON g.id_genealogia_pai = c.id_genealogia
    
)
SELECT nivel
     , arvore 
  FROM cte_recursiva
     ;

Sem stress...