Database Reference
In-Depth Information
cte
is the name of the CTE in
Example 7-28
, defined using a
SELECT
statement to contain
three columns:
tract_id
,
county_code
, and
cnt_tracts
. The main
SELECT
refers to
the CTE.
You can stuff as many CTEs as you like, separated by commas, in the
WITH
clause, as
shown in
Example 7-29
. The order of the CTEs matters in that CTEs defined later can
call CTEs defined earlier, but not vice versa.
Example 7-29. Multiple CTEs
WITH
cte1
AS
(
SELECT
tract_id
,
substring
(
tract_id
,
1
,
5
)
As
county_code
,
COUNT
(
*
)
OVER
(
PARTITION
BY
substring
(
tract_id
,
1
,
5
))
As
cnt_tracts
FROM
census
.
lu_tracts
),
cte2
AS
(
SELECT
MAX
(
tract_id
)
As
last_tract
,
county_code
,
cnt_tracts
FROM
cte1
WHERE
cnt_tracts
<
8
GROUP
BY
county_code
,
cnt_tracts
)
SELECT
c
.
last_tract
,
f
.
fact_type_id
,
f
.
val
FROM
census
.
facts
As
f
INNER
JOIN
cte2
c
ON
f
.
tract_id
=
c
.
last_tract
;
Writable CTEs
The writable CTE was introduced in version 9.1 and extends the CTE to allow for update,
delete, and insert statements. We'll revisit our logs tables that we created in
Example 6-2
, adding another child table and populating it:
CREATE
TABLE
logs_2011_01_02
(
PRIMARY
KEY
(
log_id
),
CONSTRAINT
chk
CHECK
(
log_ts
>=
'2011-01-01'
AND
log_ts
<
'2011-03-01'
)
)
INHERITS
(
logs_2011
);
In
Example 7-30
, we move data from our parent 2011 table to our new child Jan-Feb
2011 table. The
ONLY
keyword is described in
“Restricting DELETE, UPDATE, SELECT
from Inherited Tables” on page 127
and the
RETURNING
keyword in
“Returning Affected
Records to the User” on page 128
.
Example 7-30. Writable CTE moving data from one branch to another
WITH
t
AS
(
DELETE
FROM
ONLY
logs_2011
WHERE
log_ts
<
'2011-03-01'
RETURNING
*
