Cláusula WITH de PostgreSQL

En PostgreSQL, la cláusula WITH proporciona una manera de escribir sentencias auxiliares para usarlas en consultas más grandes.

La cláusula WITH ayuda a descomponer consultas grandes y complejas en formas más simples, facilitando la lectura. Estas sentencias suelen denominarse expresiones de tabla común (Common Table Expression, CTE) y también pueden considerarse como una tabla temporal que existe para la consulta.

La cláusula WITH es especialmente útil cuando se ejecutan subconsultas varias veces, ya que nos permite referenciarla por su nombre (posiblemente varias veces) dentro de la consulta.

La cláusula WITH debe definirse antes de usarse.

Sintaxis

La sintaxis básica de una consulta WITH es la siguiente:

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]

name_for_summary_dataes el nombre de la cláusula WITH,name_for_summary_datapuede ser el mismo que el nombre de una tabla existente y tiene prioridad.

Se pueden utilizar sentencias INSERT, UPDATE o DELETE de datos dentro de WITH, lo que permite realizar múltiples operaciones diferentes en la misma consulta.

WITH recursivo

En la cláusula WITH se pueden utilizar los datos que ella misma produce.

La expresión de tabla común (CTE) tiene una ventaja importante: puede referenciarse a sí misma, creando así una CTE recursiva. Una CTE recursiva es una expresión de tabla común que ejecuta repetidamente la CTE inicial para devolver subconjuntos de datos hasta obtener el conjunto de resultados completo.

Ejemplos

Crear la tabla COMPANY (Descargar el archivo SQL de COMPANY), con los siguientes datos:

ejemplodb# 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)

A continuación, usaremos la cláusula WITH para consultar datos en la tabla anterior:

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

El resultado es el siguiente:

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)

A continuación, usemos laRECURSIVEpalabra clave y la cláusula WITH para escribir una consulta que busque los datos del campoSALARY (salario)menores que 20000 y calcule su suma:

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

El resultado es el siguiente:

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

Ahora crearemos una tabla COMPANY1 similar a la tabla COMPANY, y usaremos la sentencia DELETE con la cláusula WITH para eliminar de la tabla COMPANY los datos del campoSALARY (salario)mayores o iguales que 30000, e insertaremos los datos eliminados en la tabla COMPANY1, logrando así transferir los datos de la tabla COMPANY a la tabla COMPANY1:

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);

El resultado es el siguiente:

INSERT 0 3

En este momento, los datos de las tablas COMPANY y COMPANY1 son los siguientes:

ejemplodb=# 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)


ejemplodb=# 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)

Otras extensiones