Common table expressions e consultas recursivas com WITH

O que é

WITH name AS (SELECT ...) define um resultado temporário com nome para a consulta principal; uma instrução pode definir várias, e cada uma pode referir-se às anteriores. A documentação do PostgreSQL descreve a forma recursiva: WITH RECURSIVE t AS (non-recursive term UNION [ALL] recursive term) avalia o termo não recursivo, coloca as linhas resultantes numa tabela de trabalho e depois avalia repetidamente o termo recursivo, com a tabela de trabalho no lugar da autorreferência, até que não volte nenhuma linha. UNION descarta linhas duplicadas, UNION ALL mantém-nas. A mesma página documenta SEARCH DEPTH FIRST | BREADTH FIRST BY ... SET col para ordenar a saída e CYCLE col SET is_cycle USING path para interromper a recursão em caso de ciclos. Indica ainda que, por padrão, uma consulta WITH não recursiva, sem efeitos colaterais e referenciada uma única vez é incorporada na consulta pai, enquanto MATERIALIZED força a avaliação em separado e NOT MATERIALIZED força a incorporação.

Por que importa

Hierarquias (categorias, organogramas, comentários em cadeia, listas de componentes) não cabem num número fixo de joins. Uma CTE recursiva lida com profundidade arbitrária numa única instrução; as CTEs não recursivas dividem uma consulta longa em etapas nomeadas, que podem ser lidas e testadas uma a uma.

Como aplicar

• Cadeia de ancestrais: parta do nó (SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = $1) e faça o join do termo recursivo por parent_id; para os descendentes, percorre-se no sentido oposto. Propague depth e limite-o (WHERE depth < 50) como proteção contra ciclos inesperados.
• Em grafos que possam conter ciclos, use UNION ou a cláusula CYCLE; UNION ALL num ciclo nunca termina sem um limite.
• Use SEARCH DEPTH FIRST BY name SET ord com ORDER BY ord para apresentar uma árvore em ordem aninhada.
• CTEs que modificam dados movem linhas numa só instrução: WITH moved AS (DELETE FROM live WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.
• Uma CTE referenciada mais de uma vez é materializada por padrão. Escreva MATERIALIZED numa CTE de uso único apenas para forçar a avaliação em separado (por exemplo, uma função cara calculada uma só vez), e NOT MATERIALIZED numa CTE com várias referências quando cada uso precisa só de uma fatia pequena e favorável a índices, como mostra o exemplo big_table da documentação.

Armadilhas

Uma CTE materializada é avaliada tal como foi escrita, de modo que o WHERE externo não pode ser empurrado para dentro dela e os índices das tabelas base não servem ao predicado externo. Todas as linhas que um termo recursivo produz permanecem no resultado, por isso linhas largas em árvores grandes saem caras. A documentação classifica a ordem de saída em largura (breadth-first) como um detalhe de implementação em que não se deve confiar, e a ordem dentro de um mesmo nível como indefinida; use SEARCH ou um ORDER BY explícito.