Menu

CTE no PostgreSQL: consultas WITH e WITH RECURSIVE

Uma expressão de tabela comum dá nome a uma subconsulta numa cláusula WITH para que a consulta principal a leia como uma tabela. WITH RECURSIVE permite que ela se refira a si mesma para percorrer árvores e grafos.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

Uma expressão de tabela comum (CTE) é uma subconsulta com nome numa cláusula WITH antes da instrução principal: WITH name AS (SELECT ...) SELECT ... FROM name. A consulta principal lê a CTE como uma tabela, então subconsultas aninhadas viram etapas que você lê de cima para baixo.

A CTE totals soma os pedidos de cada cliente, e a consulta principal fica com os clientes acima de 100: cy com 300 e ana com 180. Uma CTE só existe na instrução a que pertence.

Encadear várias CTEs

Separe as CTEs com vírgulas. Cada uma pode ler as CTEs definidas antes dela.

average lê totals, e a consulta principal lê as duas. Os totais por cliente são 180, 70 e 300 e a média é cerca de 183, então só cy fica acima dela.

Percorrer uma árvore com WITH RECURSIVE

WITH RECURSIVE permite que uma CTE se refira a si mesma. Ela tem duas partes unidas por UNION ALL:

  • a consulta âncora, que retorna as linhas iniciais;
  • a consulta recursiva, que faz join da CTE com a tabela para achar o próximo nível.

O PostgreSQL roda a parte recursiva sobre as linhas que a etapa anterior produziu e para quando uma etapa não retorna linhas.

Cada etapa encontra as pessoas que se reportam às linhas da etapa anterior. Mia termina com profundidade 3 e o caminho Rosa > Omar > Ivo > Mia. Para listar todo mundo abaixo de um gestor em vez da árvore inteira, troque o WHERE da âncora por id = 2.

Para subir, de um funcionário até o topo, troque a condição do join por s.id = c.manager_id.

Parar uma CTE recursiva infinita

Uma CTE recursiva só termina quando uma etapa não retorna nada. Com dados que formam um ciclo (A se reporta a B, B se reporta a A), ela nunca termina. Adicione um limite de profundidade como WHERE c.depth < 20, ou use a cláusula CYCLE (PostgreSQL 14+), que marca a primeira linha repetida e para de segui-la:

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM staff WHERE id = 5
  UNION ALL
  SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;

Gerar linhas com uma CTE recursiva

A parte recursiva não precisa de tabela. Isto conta de 1 a 5 devolvendo o valor de cada linha para a próxima volta:

Para séries simples de números ou datas, generate_series é mais curto.

Mover linhas com uma CTE que altera dados

Uma CTE pode ser um INSERT, UPDATE ou DELETE com cláusula RETURNING, e a instrução principal lê as linhas retornadas. Isto move pedidos antigos para uma tabela de arquivo numa única instrução:

Os pedidos 1 e 3 são apagados e inseridos no arquivo juntos, então uma falha deixa as duas tabelas como estavam. Todas as partes da instrução enxergam o mesmo snapshot: a consulta principal não vê o efeito do DELETE em orders, só o que o RETURNING repassa (veja RETURNING).

Controlar a incorporação com MATERIALIZED

Desde o PostgreSQL 12, uma CTE sem efeitos colaterais que a consulta principal referencia uma vez é incorporada a ela. O planejador otimiza tudo junto com a consulta externa, então um WHERE escrito fora da CTE ainda pode usar um índice. Uma CTE referenciada duas ou mais vezes é calculada uma vez e o resultado é reaproveitado. Você pode forçar qualquer um dos dois comportamentos:

WITH recent AS MATERIALIZED (
  SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;

WITH recent AS NOT MATERIALIZED (...)

MATERIALIZED calcula a CTE uma única vez, separadamente, e mantém o WHERE externo fora dela. Use quando o plano incorporado ficar mais lento. NOT MATERIALIZED pede a incorporação mesmo quando a CTE é referenciada mais de uma vez. CTEs recursivas e CTEs que alteram dados são sempre calculadas separadamente.

Perguntas frequentes

Uma CTE é mais rápida que uma subconsulta no PostgreSQL?

Normalmente nenhuma é mais rápida. Desde o PostgreSQL 12, uma CTE referenciada uma única vez e sem efeitos colaterais é incorporada à consulta principal, então recebe o mesmo plano da subconsulta equivalente. Antes da versão 12, toda CTE era calculada separadamente, o que podia impedir o uso de índices.

Posso usar uma CTE num UPDATE ou DELETE?

Pode. O WITH pode vir antes de INSERT, UPDATE, DELETE e MERGE, e a própria CTE pode ser um INSERT, UPDATE ou DELETE com RETURNING, cujas linhas a instrução principal lê.

Como evitar um loop infinito numa CTE recursiva?

Dê à parte recursiva uma condição que em algum momento não retorne linhas, como um limite de profundidade (WHERE depth < 10). Para dados que podem voltar para si mesmos, a cláusula CYCLE (PostgreSQL 14+) marca a linha repetida e para ali.

Uma CTE pode ser usada em mais de uma consulta?

Não. Uma CTE só existe na instrução em que foi escrita. Para reaproveitar uma consulta em várias instruções, crie uma view ou uma tabela temporária.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar