In PostgreSQL, the WITH inquiry gives an approach to compose helper proclamations for use in a bigger question. It helps in separating convoluted and huge questions into more straightforward structures, which are effectively decipherable. These assertions frequently alluded to as Common Table Expressions or CTEs, can be considered as characterizing transitory tables that exist only for one question.
The WITH question being CTE inquiry, is especially helpful when subquery is executed on different occasions. It is similarly useful instead of impermanent tables. It figures the collection once and permits us to reference it by its name (might be on numerous occasions) in the questions.
The WITH proviso should be characterized before it is utilized in the question.
Syntax
The fundamental language structure of WITH inquiry is as per the following −
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]
Where name_for_summary_data is the name given to the WITH provision. The name_for_summary_data can be equivalent to a current table name and will come first.
You can utilize information adjusting explanations (INSERT, UPDATE or DELETE) in WITH. This permits you to play out a few unique activities in a similar inquiry.
Recursive WITH
Recursive WITH or Hierarchical questions, is a type of CTE where a CTE can reference to itself, i.e., a WITH inquiry can allude to its own yield, consequently the name recursive.
Example
Consider the table COMPANY having records as follows −
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)
Presently, let us compose an inquiry utilizing the WITH statement to choose the records from the above table, as follows −
With CTE AS
(Select
ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;
The above given PostgreSQL articulation will create the accompanying outcome −
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)
Presently, let us compose a question utilizing the RECURSIVE watchword alongside the WITH provision, to discover the amount of the pay rates under 20000, as follows −
WITH RECURSIVE t(n) AS (
VALUES (0)
UNION ALL
SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;
The above given PostgreSQL articulation will create the accompanying outcome −
sum
-------
25000
(1 row)
Allow us to compose a question utilizing information altering articulations alongside the WITH provision, as demonstrated beneath.
To start with, make a table COMPANY1 like the table COMPANY. The inquiry in the model viably moves columns from COMPANY to COMPANY1. The DELETE in WITH erases the predetermined columns from COMPANY, restoring their substance by methods for its RETURNING condition; and afterward the essential inquiry peruses that yield and embeds it into 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);
The above given PostgreSQL articulation will deliver the accompanying outcome −
INSERT 0 3
Presently, the records in the tables COMPANY and COMPANY1 are as per the following −
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)