Views e CTEs recursivas

Views: consultas com nome

Uma view (VIEW) é uma consulta guardada à qual você dá um nome. Ela não armazena dados: cada vez que você a usa, seu SELECT é executado por baixo. Serve para não repetir consultas complexas e para dar nomes claros à equipe.

CREATE VIEW produtos_caros AS
SELECT nome, categoria, preco
FROM produtos
WHERE preco >= 200;

A partir daí, você a usa como se fosse uma tabela:

SELECT * FROM produtos_caros ORDER BY preco DESC;
SELECT categoria, COUNT(*) FROM produtos_caros GROUP BY categoria;

Uma view sempre reflete os dados atuais das tabelas base: se os produtos mudam, a view os mostra na hora (não há cópia para atualizar).

CTEs: o WITH

Uma CTE (Common Table Expression) é uma consulta temporária com nome que só vive durante a consulta em que é declarada, com WITH:

WITH caros AS (
  SELECT * FROM produtos WHERE preco >= 200
)
SELECT categoria, AVG(preco) FROM caros GROUP BY categoria;

Frente a uma view, a CTE é local (não fica guardada) e torna a consulta mais legível ao separá-la em passos.

CTEs recursivas (intro)

Com WITH RECURSIVE uma CTE pode referir-se a si mesma, ideal para gerar séries ou percorrer hierarquias. Tem duas partes unidas por UNION ALL:

WITH RECURSIVE numeros(n) AS (
  SELECT 1                       -- caso base: o ponto de partida
  UNION ALL
  SELECT n + 1 FROM numeros      -- passo recursivo: usa a linha anterior
  WHERE n < 5                    -- condição de parada (imprescindível!)
)
SELECT n FROM numeros;

• Caso base: as primeiras linhas, sem recursão.
• Passo recursivo: executa-se uma e outra vez sobre as linhas já geradas.
• Condição de parada: sem ela, a recursão seria infinita.

Exemplos

Criar uma view e consultá-la

CREATE VIEW produtos_caros AS
SELECT nome, categoria, preco
FROM produtos
WHERE preco >= 200;

SELECT * FROM produtos_caros ORDER BY preco DESC;

Gerar a série 1..5 com uma CTE recursiva

WITH RECURSIVE numeros(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numeros WHERE n < 5
)
SELECT n FROM numeros ORDER BY n;