Como funcionam CTEs e consultas recursivas no PostgreSQL?

Question

Accepted Answer

Uma **CTE** (Common Table Expression), definida com **`WITH`**, é um conjunto de resultados temporário nomeado que torna consultas complexas legíveis. PostgreSQL oferece suporte completo a CTEs, incluindo **CTEs recursivas** (`WITH RECURSIVE`) para atravessar dados hierárquicos/em árvore.

## CTEs básicas — construção de consultas legíveis

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTEs decompõem uma consulta complexa em etapas nomeadas e legíveis (encadeadas, cada uma referenciando as anteriores) — muito mais clara do que subconsultas aninhadas.

## CTEs recursivas — atravessando hierarquias

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

Uma **CTE recursiva** possui um caso base (as linhas iniciais) e uma etapa recursiva (`UNION ALL` unindo de volta à própria CTE), iterando até que não haja novas linhas — perfeito para **árvores e hierarquias** (organogramas, árvores de categorias, lista de materiais, caminhos em grafos).

## Uma nota sobre materialização de CTE (específica do Postgres)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Antes do Postgres 12, as CTEs eram sempre uma "cerca de otimização" (materializadas); agora podem ser embutidas para melhor desempenho — `MATERIALIZED`/`NOT MATERIALIZED` controla isso.

## Por que isso importa

CTEs e consultas recursivas são recursos PostgreSQL valiosos para **legibilidade e dados hierárquicos**, portanto compreendê-los é útil para escrever consultas complexas sustentáveis e lidar com dados estruturados em árvore.

O benefício cotidiano é a **legibilidade**: consultas complexas com múltiplos níveis de subconsultas tornam-se difíceis de manter, enquanto as CTEs permitem decompô-las em etapas lógicas sequenciais nomeadas (lidas de cima para baixo), melhorando dramaticamente a clareza — tornando as CTEs uma ferramenta importante para escrever SQL sustentável.

A capacidade mais relevante é a **CTE recursiva** (`WITH RECURSIVE`), que permite consultar **dados hierárquicos e estruturados em árvore** (organogramas, árvores de categorias, lista de materiais, comentários encadeados, traversal de grafos) — uma classe de consultas de outra forma muito difícil em SQL, tornando as CTEs recursivas a solução padrão para traversal hierárquico.

Compreender a estrutura de CTE recursiva (caso base + etapa recursiva unindo de volta a si mesma) é conhecimento-chave para essa necessidade comum.

Um detalhe útil e específico do Postgres é o comportamento de **materialização de CTE** (Postgres 12+ pode embutir CTEs para melhor desempenho, controlável com `MATERIALIZED`/`NOT MATERIALIZED`) — relevante para otimização.

Como consultas complexas são comuns (e a legibilidade importa para manutenção), e como dados hierárquicos (que CTEs recursivas tratam particularmente bem) aparecem com frequência (estruturas organizacionais, categorias aninhadas), compreender CTEs e consultas recursivas é conhecimento PostgreSQL valioso e praticamente relevante que melhora a sustentabilidade de consultas e desbloqueia queryagem hierárquica — uma capacidade frequentemente relevante para SQL limpo e sofisticado e um tópico comum, especialmente a forma recursiva para a necessidade recorrente de consultar dados estruturados em árvore.