Components of a CTE
This simple example shows the components of a common table expression.
CREATE TABLE s (c INT, i INT);
INSERT INTO s VALUES (10,1),(20,2),(30,3),(100,15),(200,15),(300,15);
COMMIT;
/* Define the CTE expression and column list */
WITH t(z) AS
/* Define the CTE query */
(
SELECT c FROM s WHERE i > 10
)
/*Define outer query that references the CTE name */
SELECT t.z FROM t ORDER BY 1;
Executing . . .
z
-------------
100
200
300
(3 rows)
The t is the name of the first (and in this case only) WITH element (also known as a CTE). There can be multiple WITH elements in the same query. Each WITH element can be referred to by its name as though it were a view. The scope of each WITH element extends to the right through the rest of the SQL statement. WITH elements can refer to others that have been declared already in the current statement. For example:
WITH
t(z) AS (
SELECT c FROM s WHERE i > 10
),
u(a,i) AS (
SELECT z, 15 FROM t
)
SELECT u.a,s.i FROM u,s WHERE s.i=u.i ORDER BY 1;
Executing . . .
a i
------------- ------------
100 15
100 15
100 15
200 15
200 15
200 15
300 15
300 15
300 15
(9 rows)