7.8. COM Consultas (Expressões de Tabelas Comuns)

7.8.1. SELECT in WITH

O valor básico do SELECT in WITH é decompor as consultas complicadas em partes mais simples. Um exemplo é:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) )SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

que exibe os totais de vendas por produto apenas nas regiões de vendas superiores. A cláusula WITH define duas instruções auxiliares chamadas regional_sales e top_regions, onde a saída de regional_sales é usada em top_regions e a saída de top_regions é usada na consulta primária SELECT. Este exemplo poderia ter sido escrito sem WITH, mas teríamos precisado de dois níveis de subSELECTs aninhados. É um pouco mais fácil de seguir este caminho.

O modificador opcional RECURSIVE muda COM uma mera conveniência sintáctica para uma funcionalidade que realiza coisas que de outra forma não seriam possíveis no SQL padrão. Usando o RECURSIVE, uma consulta WITH pode se referir à sua própria saída. Um exemplo muito simples é esta consulta para somar os números inteiros de 1 a 100:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

A forma geral de uma consulta recursiva WITH é sempre um termo não recursivo, depois UNION (ou UNION ALL), depois um termo recursivo, onde apenas o termo recursivo pode conter uma referência à própria saída da consulta. Tal consulta é executada da seguinte forma:

No exemplo acima, a tabela de trabalho tem apenas uma única linha em cada passo, e assume os valores de 1 a 100 em passos sucessivos. No 100º passo, não há saída por causa da cláusula WHERE, e assim a consulta termina.

As consultas recursivas são tipicamente usadas para lidar com dados hierárquicos ou estruturados em árvore. Um exemplo útil é esta consulta para encontrar todas as sub-partes diretas e indiretas de um produto, dada apenas uma tabela que mostra inclusões imediatas:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part )SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

Ao trabalhar com consultas recursivas é importante ter certeza de que a parte recursiva da consulta eventualmente não retornará nenhum tuples, ou então a consulta irá loopar indefinidamente. Algumas vezes, usando UNION ao invés de UNION ALL pode conseguir isso descartando as linhas que duplicam as linhas de saída anteriores. No entanto, muitas vezes um ciclo não envolve linhas de saída que estão completamente duplicadas: pode ser necessário verificar apenas um ou alguns campos para ver se o mesmo ponto foi atingido antes. O método padrão para lidar com tais situações é calcular uma matriz dos valores já visitados. Por exemplo, considere a seguinte consulta que pesquisa um gráfico de tabela usando um campo de link:

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link)SELECT * FROM search_graph;

Esta consulta fará um loop se as relações de link contiverem ciclos. Como precisamos de uma saída de “profundidade”, apenas mudar UNION ALL para UNION não eliminaria o looping. Ao invés disso, precisamos reconhecer se atingimos a mesma linha novamente enquanto seguimos um caminho particular de links. Adicionamos duas colunas caminho e ciclo à consulta de loop:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

Para evitar ciclos, o valor do array é muitas vezes útil por direito próprio como representando o “caminho” tomado para alcançar qualquer linha em particular.

No caso geral onde mais de um campo precisa ser verificado para reconhecer um ciclo, use um array de linhas. Por exemplo, se precisarmos comparar os campos f1 e f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

Um truque útil para testar consultas quando você não tem certeza se elas podem fazer loop é colocar um LIMIT na consulta pai. Por exemplo, esta consulta faria um loop para sempre sem o LIMIT:

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

Isso funciona porque a implementação do PostgreSQL avalia apenas tantas linhas de uma consulta WITH quanto são realmente obtidas pela consulta pai. Usar este truque na produção não é recomendado, porque outros sistemas podem funcionar de forma diferente. Além disso, normalmente não funcionará se você fizer a consulta externa ordenar os resultados da consulta recursiva ou juntá-los a alguma outra tabela, porque em tais casos a consulta externa normalmente tentará buscar toda a saída da consulta WITH de qualquer forma.

Uma propriedade útil das consultas WITH é que elas são avaliadas apenas uma vez por execução da consulta pai, mesmo que sejam referenciadas mais de uma vez pela consulta pai ou irmão WITH. Assim, cálculos caros que são necessários em vários lugares podem ser colocados dentro de uma consulta com WITH para evitar trabalho redundante. Outra aplicação possível é evitar avaliações múltiplas indesejadas de funções com efeitos colaterais. No entanto, o outro lado desta moeda é que o otimizador é menos capaz de empurrar restrições da consulta pai para uma consulta com WITH do que uma subconsulta comum. A consulta WITH será geralmente avaliada como escrita, sem supressão de linhas que a consulta pai possa descartar posteriormente. (Mas, como mencionado acima, a avaliação pode parar cedo se a(s) referência(s) para a consulta exigir apenas um número limitado de linhas.)

Os exemplos acima só mostram WITH sendo usado com SELECT, mas pode ser anexado da mesma forma a INSERT, UPDATE, ou DELETE. Em cada caso ele efetivamente fornece tabela(s) temporária(s) que podem ser referidas no comando principal.