O valor básico do SELECT no WITH é quebrar 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_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

que exibe os totais de vendas por produto somente nas regiões de maior venda. A cláusula WITH define duas declarações auxiliares denominadas 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 seriam necessários dois níveis de sub-SELECTs aninhados. É um pouco mais fácil seguir este caminho.

O modificador opcional RECURSIVE muda WITH de uma mera conveniência sintática para um recurso que realiza coisas que de outra forma não seriam possíveis no SQL padrão. Usando RECURSIVE, uma consulta WITH pode fazer referência à própria saída. Um exemplo muito simples é essa 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, seguido por UNION (ou UNION ALL) e, depois, por um termo recursivo, onde apenas o termo recursivo pode conter uma referência à própria saída da consulta. Essa consulta é executada da seguinte forma:

No exemplo acima, a tabela de trabalho tem apenas uma única linha em cada etapa e assume valores de 1 a 100 em etapas sucessivas. Na 100ª etapa não há saída devido à cláusula WHERE, portanto a consulta termina.

As consultas recursivas são normalmente usadas para lidar com dados hierárquicos ou estruturados em árvore. Um exemplo útil é essa consulta para encontrar todas as subpartes 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 * pr.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

WITH RECURSIVE fat (n, fatorial) AS (
 SELECT 0, 1                            -- Subconsulta inicial
  UNION ALL
 SELECT n+1, (n+1)*fatorial FROM fat   -- Subconsulta recursiva
        WHERE n < 9)
SELECT n, fatorial  AS "n!" FROM fat;

 n |   n!
---+--------
 0 |      1
 1 |      1
 2 |      2
 3 |      6
 4 |     24
 5 |    120
 6 |    720
 7 |   5040
 8 |  40320
 9 | 362880
(10 linhas)

WITH RECURSIVE Fibonacci(PrevN, N) AS (
   -- Membro âncora: Valores iniciais
 SELECT 0, 1
  UNION ALL
 -- Membro recursivo: Calcular o próximo valor
 SELECT N, PrevN + N
 FROM Fibonacci
 WHERE N < 100 -- Condição de término: valor inferior a 100.
)
SELECT PrevN AS Sequência
FROM Fibonacci;

 sequência
-----------
         0
         1
         1
         2
         3
         5
         8
        13
        21
        34
        55
        89
(12 linhas)

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree;

WITH RECURSIVE search_tree(id, link, data, path) AS (
    SELECT t.id, t.link, t.data, ARRAY[t.id]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || t.id
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

WITH RECURSIVE search_tree(id, link, data, path) AS (
    SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)]
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2)
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY path;

WITH RECURSIVE search_tree(id, link, data, depth) AS (
    SELECT t.id, t.link, t.data, 0
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data, depth + 1
    FROM tree t, search_tree st
    WHERE t.id = st.link
)
SELECT * FROM search_tree ORDER BY depth;

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_tree(id, link, data) AS (
    SELECT t.id, t.link, t.data
    FROM tree t
  UNION ALL
    SELECT t.id, t.link, t.data
    FROM tree t, search_tree st
    WHERE t.id = st.link
) SEARCH BREADTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 0
    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;

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

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

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
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;

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

Uma propriedade útil das consultas WITH é que elas são normalmente avaliadas apenas uma vez por execução da consulta mãe, mesmo que sejam referidas mais de uma vez pela consulta mãe ou pelas consultas irmãs WITH. Assim, computações caras necessárias em vários lugares podem ser colocadas em uma consulta WITH para evitar trabalho redundante. Outra aplicação possível é evitar múltiplas avaliações indesejadas de funções com efeitos colaterais. No entanto, o outro lado dessa moeda é que o otimizador não consegue empurrar restrições da consulta mãe para uma consulta WITH multi-referenciada, porque isto pode afetar todos os usos da saída da consulta WITH quando deveria afetar apenas um. A consulta WITH multi-referenciada será avaliada conforme escrita, sem a supressão de linhas que a consulta mãe possa descartar posteriormente. (Mas, como mencionado acima, a avaliação pode parar mais cedo se a(s) referência(s) à consulta exigir(em) apenas um número limitado de linhas).

Entretanto, se a consulta WITH for não-recursiva e sem efeitos colaterais (ou seja, for um SELECT sem funções voláteis), então ela pode ser mesclada na consulta mãe, permitindo a otimização conjunta dos dois níveis de consulta. Por padrão, isto acontece quando a consulta mãe referencia a consulta WITH apenas uma vez, mas não acontece quando referencia a consulta WITH mais de uma vez. É possível controlar este comportamento especificando MATERIALIZED para forçar a computação em separado da consulta WITH, ou especificando NOT MATERIALIZED para forçar a mesclagem na consulta mãe. A última opção arrisca a computação duplicada da consulta WITH, mas ainda pode causar uma economia se cada uso da consulta WITH precisar 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;

Esta consulta WITH será mesclada, produzindo o mesmo plano de execução que:

SELECT * FROM big_table WHERE key = 123;

Em particular, se houver um índice em key, ele será provavelmente usado para buscar apenas as linhas tendo 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.ref
WHERE w2.key = 123;

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

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

para que as restrições da consulta mãe possam ser aplicadas diretamente às varreduras de big_table.

Um exemplo de 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 seja avaliada apenas uma vez para cada linha da tabela, e não duas vezes.

Os exemplos acima mostram WITH sendo usada com SELECT, mas pode ser anexada da mesma maneira a INSERT, UPDATE, DELETE ou MERGE. Em cada caso, são de fato fornecidas tabelas temporárias que podem ser referenciadas no comando principal.

Podem ser usadas declarações de modificação de dados (INSERT, UPDATE, DELETE) ou MERGE) no WITH. Isto permite a execução de várias operações diferentes na mesma consulta. Um exemplo é:

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

Essa consulta move de fato as linhas de products para products_log. O comando DELETE em WITH exclui as linhas especificadas de products, retornando seu conteúdo por meio de sua cláusula RETURNING; e, em seguida, a consulta primária lê essa saída e a insere em products_log.

Um detalhe do exemplo acima é que a cláusula WITH é anexada ao comando INSERT, e não ao sub-SELECT dentro do INSERT. Isto é necessário, porque os comandos de modificação de dados só são permitidos em cláusulas WITH anexadas ao comando de nível superior. No entanto, as regras normais de visibilidade da cláusula WITH se aplicam, portanto é possível consultar a saída da cláusula WITH a partir do sub-SELECT.

Os comandos de modificação de dados em WITH têm geralmente a cláusula RETURNING (veja a Seção 6.4), conforme mostrado no exemplo acima. É a saída da cláusula RETURNING, e não a tabela de destino do comando modificador de dados, que forma a tabela temporária que pode ser referenciada pelo restante da consulta. Se um comando de modificação de dados em WITH não tiver uma cláusula RETURNING, então não formará uma tabela temporária, não podendo assim ser referenciado no restante da consulta. Esse comando será executado mesmo assim. Um exemplo não particularmente útil é:

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

Esse exemplo remove todas as linhas das tabelas foo e bar. O número de linhas afetadas informado ao cliente incluiria apenas as linhas removidas de bar.

Autorreferências recursivas em declarações de modificação de dados não são permitidas. Em alguns casos, é possível contornar essa limitação consultando a saída de um WITH recursivo, por exemplo:

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

Essa consulta remove todas as subpartes diretas e indiretas de um produto.

Os comandos de modificação de dados em WITH são executados apenas uma vez, e sempre até o fim, independentemente da consulta primária ler toda (ou parte) de sua saída. Repare ser diferente da regra para SELECT no WITH: conforme indicado na seção anterior, a execução do SELECT é realizada apenas até onde a consulta primária exige sua saída.

Os sub-comandos em WITH são executados simultaneamente uns com os outros, e com a consulta principal. Portanto, ao se usar comandos de modificação de dados em WITH, a ordem pela qual as atualizações especificadas realmente acontecem é imprevisível. Todos os comandos são executados no mesmo instantâneo (snapshot) (veja o Capítulo 13), portanto não podem “ver” os efeitos uns dos outros nas tabelas de destino. Isto alivia os efeitos da imprevisibilidade da ordem real das atualizações de linha, significando que os dados em RETURNING são a única maneira de comunicar alterações entre os diferentes sub-comandos WITH e a consulta principal. Um exemplo disso é que em

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

o SELECT externo retorna os preços originais, antes da ação do UPDATE, enquanto em

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

o SELECT externo retorna os valores atualizados.

Tentar atualizar a mesma linha duas vezes em um único comando não é admitido. Apenas uma das atualizações ocorre, mas não é fácil (e às vezes não é possível) prever com segurança qual delas. Isto também se aplica à exclusão de uma linha que já foi atualizada no mesmo comando: somente a atualização é realizada. Portanto, deve-se geralmente evitar tentar modificar uma mesma linha duas vezes em um único comando. Em particular, deve-se evitar escrever sub-comandos WITH que possam afetar as mesmas linhas modificadas pelo comando principal, ou um sub-comando irmão. Os efeitos de tal comando não são previsíveis.

No momento, qualquer tabela usada como destino de um comando de modificação de dados na cláusula WITH não deve ter uma regra condicional, nem uma regra ALSO, nem uma regra INSTEAD que se expanda para vários comandos.