PostgreSQL - cláusula WITH

No PostgreSQL, a consulta WITH fornece uma maneira de escrever instruções auxiliares para uso em uma consulta maior. Ajuda a dividir consultas complicadas e grandes em formulários mais simples, que são facilmente legíveis. Essas instruções, geralmente chamadas de Expressões de Tabela Comuns ou CTEs, podem ser consideradas como definições de tabelas temporárias que existem apenas para uma consulta.

A consulta WITH sendo uma consulta CTE, é particularmente útil quando a subconsulta é executada várias vezes. É igualmente útil no lugar de tabelas temporárias. Ele calcula a agregação uma vez e nos permite referenciá-la por seu nome (pode ser várias vezes) nas consultas.

A cláusula WITH deve ser definida antes de ser usada na consulta.

Sintaxe

A sintaxe básica da consulta WITH é a seguinte -

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

Onde name_for_summary_data é o nome dado à cláusula WITH. O name_for_summary_data pode ser igual a um nome de tabela existente e terá precedência.

Você pode usar instruções de modificação de dados (INSERT, UPDATE ou DELETE) em WITH. Isso permite que você execute várias operações diferentes na mesma consulta.

Recursivo COM

COM recursivo ou consultas hierárquicas, é uma forma de CTE onde um CTE pode se referir a si mesmo, ou seja, uma consulta WITH pode se referir à sua própria saída, daí o nome recursivo.

Exemplo

Considere a tabela COMPANY com os registros a seguir -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Agora, vamos escrever uma consulta usando a cláusula WITH para selecionar os registros da tabela acima, como segue -

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Agora, vamos escrever uma consulta usando a palavra-chave RECURSIVE junto com a cláusula WITH, para encontrar a soma dos salários abaixo de 20.000, como segue -

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

sum
-------
 25000
(1 row)

Vamos escrever uma consulta usando instruções de modificação de dados junto com a cláusula WITH, conforme mostrado abaixo.

Primeiro, crie uma tabela COMPANY1 semelhante à tabela COMPANY. A consulta no exemplo move efetivamente as linhas de COMPANY para COMPANY1. O DELETE em WITH apaga as linhas especificadas de COMPANY, retornando seu conteúdo por meio de sua cláusula RETURNING; e então a consulta primária lê essa saída e a insere em COMPANY1 TABLE -

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

INSERT 0 3

Agora, os registros nas tabelas COMPANY e COMPANY1 são os seguintes -

testdb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


testdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)