CTE Recursivo: o que é e pra que serve

Hoje a gente vai dar mais um passo e mexer com a mágica da recursão. Se você já programou numa linguagem que tem recursão (tipo Python mesmo), já tem uma ideia do que é. Mas relaxa, se isso parece meio misterioso — vamos explicar tudo nos mínimos detalhes.

CTE recursivo é uma ferramenta poderosa pra lidar com estruturas de dados hierárquicas, tipo árvore, como organogramas de empresas, árvore genealógica ou diretórios de arquivos.

Falando simples, são expressões que podem "chamar elas mesmas", pra ir processando todos os níveis dos dados aos poucos.

Principais características dos CTEs recursivos:

1. Usam a palavra-chave WITH RECURSIVE.
2. CTE recursivo tem duas partes:
   • Consulta base: define o ponto de partida (ou "raiz") da recursão.
   • Consulta recursiva: processa o resto dos dados, usando o resultado do passo anterior.

O algoritmo de um CTE recursivo é tipo subir uma escada:

• Primeiro você pisa no primeiro degrau (essa é a consulta base).
• Depois sobe pro segundo degrau, usando o resultado do primeiro (consulta recursiva).
• Esse processo repete até acabar os degraus (atingir a condição de parada).

Sintaxe do CTE recursivo

Bora ver logo um exemplo padrão:

WITH RECURSIVE cte_name AS (
    -- Consulta base
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- Consulta recursiva
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

Papel do UNION e UNION ALL em CTE recursivo

Todo CTE recursivo tem que usar os operadores UNION ou UNION ALL entre a parte base e a recursiva.

Qual operador escolher: UNION ou UNION ALL?

Se você não sabe qual usar — quase sempre vai de UNION ALL. Por quê? Porque ele é mais rápido: só junta os resultados, sem checar se tem duplicata. Ou seja — menos processamento, menos recurso e resultado mais rápido.

Isso é ainda mais importante em CTEs recursivos. Quando você monta hierarquias — tipo árvore de comentários ou estrutura de chefia numa empresa — UNION ALL é quase sempre o certo. Se usar só UNION, o banco pode achar que alguns passos já foram feitos e "cortar" parte do resultado. Isso quebra toda a lógica da recursão.

Usa UNION só se você tem certeza que duplicata é ruim e precisa sumir com elas. Mas lembra: sempre é um equilíbrio entre limpeza e velocidade.

Exemplo de abordagens diferentes

-- UNION: duplicatas são excluídas
SELECT 'A'
UNION
SELECT 'A';     -- Resultado: uma linha 'A'

-- UNION ALL: duplicatas ficam
SELECT 'A'
UNION ALL
SELECT 'A';     -- Resultado: duas linhas 'A'

Em consultas recursivas é mais seguro sempre usar UNION ALL, pra não perder passos importantes na hora de percorrer a estrutura.

Vamos ver um caso típico: temos uma tabela de funcionários com as colunas employee_id, manager_id e name. Precisamos montar a hierarquia, começando pelo diretor — a pessoa sem chefe (manager_id = NULL).

Supondo que temos a tabela de funcionários: employees

A gente precisa entender quem responde pra quem, e descobrir o nível de cada funcionário na estrutura. Isso é útil, por exemplo, pra mostrar a árvore de funcionários na interface ou montar um relatório da estrutura do time.

WITH RECURSIVE employee_hierarchy AS (
    -- Começa com quem não tem chefe
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Adiciona subordinados e aumenta o nível
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

O resultado vai ser assim:

Essa consulta mostra direitinho como "percorrer" a hierarquia dos funcionários — do diretor até os mais novinhos na estrutura. O campo level é ótimo pra formatar ou visualizar a árvore.

Exemplo: categorias de produtos

Agora imagina que a gente trabalha com uma tabela de categorias de produtos, onde cada categoria pode ter subcategorias, e essas também podem ter suas subcategorias. Como montar a árvore de categorias?

Tabela categories

Consulta recursiva:

WITH RECURSIVE category_tree AS (
    -- Caso base: achar as categorias raiz
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Parte recursiva: achar subcategorias das categorias atuais
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Resultado:

Agora dá pra ver a árvore de categorias com os níveis de profundidade.

Por que CTE recursivo é massa?

CTE recursivo é uma das ferramentas mais expressivas e poderosas do SQL. Em vez de lógica aninhada complicada, você só diz onde começa (caso base) e como continuar (parte recursiva) — o resto o PostgreSQL faz pra você.

Na real, esse tipo de consulta é usado direto pra percorrer hierarquias: funcionários, categorias de produto, diretórios no disco, grafos em redes sociais. E é fácil de expandir: se entrar dado novo na tabela, a consulta já pega. Prático e escalável.

Mas tem uns perrengues. Sempre presta atenção nas condições de parada — sem elas a consulta pode entrar em loop infinito. Não esquece dos índices: em tabelas grandes, consulta recursiva sem índice pode travar tudo. E UNION ALL é quase sempre a melhor escolha, principalmente em tarefas hierárquicas, senão você pode perder passos da recursão por causa da remoção de duplicatas.

Um CTE recursivo bem feito deixa você expressar lógica de negócio complexa em poucas linhas — sem procedures, loops ou código extra. É aquele caso em que o SQL fica não só certo, mas bonito.

Erros comuns ao trabalhar com CTE recursivo

• Recursão infinita: se você não colocar uma condição de parada (WHERE) direito, a consulta pode ficar em loop.
• Dados demais: usar UNION ALL errado pode trazer duplicata.
• Performance: consultas recursivas podem pesar em tabelas grandes. Índices nas colunas chave (tipo manager_id) ajudam a acelerar.

Quando não dá pra fugir de consulta recursiva

Parece que consulta recursiva é coisa de teoria, mas na real aparece direto no dia a dia do dev. Por exemplo:

• pra montar relatórios de estrutura de empresa ou classificação de produtos;
• pra percorrer árvore de pastas e pegar todas as subpastas;
• pra analisar grafos — conexões sociais, rotas, dependências entre tarefas;
• pra simplesmente mostrar relações complexas entre objetos de um jeito legível.

Se você precisa percorrer uma estrutura onde uma coisa depende da outra — quase certeza que vai usar WITH RECURSIVE.