1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
# name: test/sql/cte/test_recursive_cte_tutorial.test
# description: Test Recursive CTEs from the CyberTec tutorial: https://www.cybertec-postgresql.com/en/recursive-queries-postgresql/
# group: [cte]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE emp (empno INTEGER PRIMARY KEY,
ename VARCHAR,
job VARCHAR,
mgr INTEGER,
hiredate DATE,
sal DOUBLE,
comm DOUBLE,
deptno INTEGER);
statement ok
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, DATE '1981-11-17', 5000.00, NULL, 10);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, DATE '1981-05-01', 2850.00, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, DATE '1981-06-09', 2450.00, NULL, 10);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, DATE '1981-04-02', 2975.00, NULL, 20);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, DATE '1981-12-03', 3000.00, NULL, 20);
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, DATE '1980-12-17', 800.00, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, DATE '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, DATE '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, DATE '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, DATE '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, DATE '1981-12-03', 950.00, NULL, 30);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, DATE '1982-01-23', 1300.00, NULL, 10);
query II
WITH RECURSIVE ctename AS (
SELECT empno, ename
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
----
7566 JONES
7902 FORD
7369 SMITH
query III
WITH RECURSIVE ctename AS (
SELECT empno, ename,
0 AS level
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename,
ctename.level + 1
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
----
7566 JONES 0
7902 FORD 1
7369 SMITH 2
query III
WITH RECURSIVE ctename AS (
SELECT empno, ename,
ename AS path
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename,
ctename.path || ' -> ' || emp.ename
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename;
----
7566 JONES JONES
7902 FORD JONES -> FORD
7369 SMITH JONES -> FORD -> SMITH
statement ok
CREATE VIEW ctenames AS (
WITH RECURSIVE ctename AS (
SELECT empno, ename,
ename AS path
FROM emp
WHERE empno = 7566
UNION ALL
SELECT emp.empno, emp.ename,
ctename.path || ' -> ' || emp.ename
FROM emp
JOIN ctename ON emp.mgr = ctename.empno
)
SELECT * FROM ctename
);
query III
SELECT * FROM ctenames;
----
7566 JONES JONES
7902 FORD JONES -> FORD
7369 SMITH JONES -> FORD -> SMITH
require no_alternative_verify
statement ok
PRAGMA disable_verification
query II
WITH RECURSIVE fib AS (
SELECT 1 AS n,
1::bigint AS "fibₙ",
1::bigint AS "fibₙ₊₁"
UNION ALL
SELECT n+1,
"fibₙ₊₁",
"fibₙ" + "fibₙ₊₁"
FROM fib
)
SELECT n, "fibₙ" FROM fib
LIMIT 20;
----
1 1
2 1
3 2
4 3
5 5
6 8
7 13
8 21
9 34
10 55
11 89
12 144
13 233
14 377
15 610
16 987
17 1597
18 2584
19 4181
20 6765
|
