PostgreSQL - Cláusula WITH

En PostgreSQL, la consulta WITH proporciona una forma de escribir declaraciones auxiliares para usar en una consulta más grande. Ayuda a dividir consultas complicadas y grandes en formas más simples, que son fácilmente legibles. Estas declaraciones a menudo denominadas Expresiones de tabla comunes o CTE, se pueden considerar como la definición de tablas temporales que existen solo para una consulta.

La consulta WITH, que es una consulta CTE, es particularmente útil cuando la subconsulta se ejecuta varias veces. Es igualmente útil en lugar de tablas temporales. Calcula la agregación una vez y nos permite hacer referencia a ella por su nombre (puede ser varias veces) en las consultas.

La cláusula WITH debe definirse antes de que se utilice en la consulta.

Sintaxis

La sintaxis básica de la 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]

Donde name_for_summary_data es el nombre dado a la cláusula WITH. El name_for_summary_data puede ser el mismo que un nombre de tabla existente y tendrá prioridad.

Puede usar declaraciones de modificación de datos (INSERT, UPDATE o DELETE) en WITH. Esto le permite realizar varias operaciones diferentes en la misma consulta.

Recursivo CON

Las consultas CON o jerárquicas recursivas son una forma de CTE en la que un CTE puede hacer referencia a sí mismo, es decir, una consulta WITH puede hacer referencia a su propia salida, de ahí el nombre recursivo.

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

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)

Ahora, escribamos una consulta usando la cláusula WITH para seleccionar los registros de la tabla anterior, de la siguiente manera:

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

La declaración de PostgreSQL dada anteriormente producirá el siguiente 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)

Ahora, escribamos una consulta usando la palabra clave RECURSIVE junto con la cláusula WITH, para encontrar la suma de los salarios menores a 20000, de la siguiente manera:

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

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

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

Escribamos una consulta utilizando declaraciones de modificación de datos junto con la cláusula WITH, como se muestra a continuación.

Primero, cree una tabla EMPRESA1 similar a la tabla EMPRESA. La consulta del ejemplo mueve filas de forma efectiva de EMPRESA a EMPRESA1. El DELETE en WITH elimina las filas especificadas de COMPANY, devolviendo su contenido mediante su cláusula RETURNING; y luego la consulta principal lee ese resultado y lo inserta en 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);

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

INSERT 0 3

Ahora, los registros de las tablas EMPRESA y EMPRESA1 son los siguientes:

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)

↰ Previous page Next page ↱