PostgreSQL: Documentação: 13: 7.8. Com consultas (expressões de tabelas comuns)

o valor básico de SELECT em WITH é dividir 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 totais de vendas por produto em apenas as regiões de vendas de topo. A cláusula WITH define duas declaraçõ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 sub-SELECTS. é um pouco mais fácil de seguir por este caminho.

o opcional RECURSIVE modificador muda WITH de uma mera conveniência sintática para uma característica que realiza coisas que de outra forma não seriam possíveis em SQL padrão. Usando RECURSIVE, uma consulta WITH pode se referir à sua própria saída. Um exemplo muito simples é esta consulta para somar os 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, então UNION (ou UNION ALL), então 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 linha em cada passo, e assume os valores de 1 a 100 em etapas sucessivas. Na 100ª etapa, não há saída por causa da cláusula WHERE, e assim a consulta termina.

consultas recursivas são tipicamente usadas para lidar com dados hierárquicos ou estruturados em árvores. Um exemplo útil é esta consulta para encontrar todos os diretos e indiretos sub-partes de um produto, tendo apenas uma tabela que mostra imediato inclusões:

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

Quando se trabalha com consultas recursivas é importante certifique-se de que a parte recursiva da consulta retornará eventualmente não tuplas, ou, então, a consulta irá repetir indefinidamente. Às vezes, usando UNION em vez 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 são completamente duplicadas: pode ser necessário verificar apenas um ou alguns campos para ver se o mesmo ponto foi alcançado 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 procura numa tabela graph usando um campo 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á loop se as relações link contiverem ciclos. Porque precisamos de uma saída de “profundidade”, apenas mudando UNION ALL para UNION não eliminaria o looping. Em vez disso, precisamos reconhecer se chegamos à mesma linha novamente, enquanto seguimos um caminho particular de links. Nós adicionamos duas colunas path e cycle à consulta propensa ao 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;

além de prevenir ciclos, o valor do array é muitas vezes útil em seu próprio direito como representando o “caminho” tomado para chegar a qualquer linha particular.

no caso geral onde mais de um campo precisa ser verificado para reconhecer um ciclo, use um array de linhas. Por exemplo, se fosse preciso 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 eles podem loop é colocar um LIMIT na consulta Pai. Por exemplo, esta consulta seria 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;

isto funciona porque a implementação do PostgreSQL avalia apenas tantas linhas de uma consulta WITH como 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á obter todos os resultados da consulta WITH de qualquer maneira.

uma propriedade útil de WITH consultas é que eles são normalmente avaliados apenas uma vez por execução da consulta-mãe, mesmo se eles são referidos mais de uma vez pela consulta-mãe ou sibling WITH consultas. Assim, cálculos caros que são necessários em vários lugares podem ser colocados dentro de uma consulta WITH para evitar trabalho redundante. Outra possível aplicação é evitar avaliações múltiplas indesejadas de funções com efeitos secundários. No entanto, o outro lado desta moeda é que o otimizador não é capaz de empurrar restrições da consulta pai para baixo em uma consulta multiply referenciada WITH, uma vez que isso pode afetar todos os usos da saída da consulta WITH quando deve afetar apenas um. A consulta multiply-referenciada WITH será avaliada como escrita, sem supressão de linhas que a consulta pai pode descartar depois. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.)

However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. Por padrão, isso acontece se a consulta Pai referencia a consulta WITH apenas uma vez, mas não se referencia a consulta WITH mais de uma vez. Você pode anular essa decisão, especificando MATERIALIZED para forçar o cálculo separado da consulta WITH, ou especificando NOT MATERIALIZED para forçá-la a ser fundido na consulta Pai. A última opção corre o risco de duplicar a computação da consulta WITH, mas ela ainda pode dar uma poupança líquida se cada uso da consulta WITH necessita apenas de uma pequena parte da saída completa da consulta WITH.

Um exemplo simples destas regras é

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

Este WITH consulta será dobrado, produzindo o mesmo plano de execução como

SELECT * FROM big_table WHERE key = 123;

Em particular, se existe um índice em key, ele provavelmente será usado para buscar apenas as linhas de ter key = 123. Por outro lado, em

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

a consulta WITH será materializada, produzindo uma cópia temporária de big_table que é então unida a si mesma — sem benefício de qualquer índice. Esta consulta será executada de forma muito mais eficiente se escrita como

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

de modo que as restrições da consulta-mãe podem ser aplicadas diretamente a varreduras de big_table.

um exemplo onde NOT MATERIALIZED pode ser indesejável é

WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

aqui, a materialização da consulta WITH garante que very_expensive_function é avaliado apenas uma vez por linha de tabela, e não duas vezes.

os exemplos acima mostram apenas WITH sendo usado com SELECT , mas pode ser ligado da mesma forma a INSERT, UPDATE, ou DELETE. Em cada caso, fornece efetivamente tabelas temporárias que podem ser referidas no comando principal.