| blob | 04383e43d2dbefb506f81a58d66d6d22f5ed1d58 |
1 --
2 -- CREATE_VIEW
3 -- Virtual class definitions
4 -- (this also tests the query rewrite system)
5 --
6 CREATE VIEW street AS
7 SELECT r.name, r.thepath, c.cname AS cname
8 FROM ONLY road r, real_city c
9 WHERE c.outline ## r.thepath;
10 CREATE VIEW iexit AS
11 SELECT ih.name, ih.thepath,
12 interpt_pp(ih.thepath, r.thepath) AS exit
13 FROM ihighway ih, ramp r
14 WHERE ih.thepath ## r.thepath;
15 CREATE VIEW toyemp AS
16 SELECT name, age, location, 12*salary AS annualsal
17 FROM emp;
18 -- Test comments
19 COMMENT ON VIEW noview IS 'no view';
20 ERROR: relation "noview" does not exist
21 COMMENT ON VIEW toyemp IS 'is a view';
22 COMMENT ON VIEW toyemp IS NULL;
23 --
24 -- CREATE OR REPLACE VIEW
25 --
26 CREATE TABLE viewtest_tbl (a int, b int);
27 COPY viewtest_tbl FROM stdin;
28 CREATE OR REPLACE VIEW viewtest AS
29 SELECT * FROM viewtest_tbl;
30 CREATE OR REPLACE VIEW viewtest AS
31 SELECT * FROM viewtest_tbl WHERE a > 10;
32 SELECT * FROM viewtest;
33 a | b
34 ----+----
35 15 | 20
36 20 | 25
37 (2 rows)
39 CREATE OR REPLACE VIEW viewtest AS
40 SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
41 SELECT * FROM viewtest;
42 a | b
43 ----+----
44 20 | 25
45 15 | 20
46 10 | 15
47 (3 rows)
49 -- should fail
50 CREATE OR REPLACE VIEW viewtest AS
51 SELECT a FROM viewtest_tbl WHERE a <> 20;
52 ERROR: cannot drop columns from view
53 -- should fail
54 CREATE OR REPLACE VIEW viewtest AS
55 SELECT 1, * FROM viewtest_tbl;
56 ERROR: cannot change name of view column "a" to "?column?"
57 -- should fail
58 CREATE OR REPLACE VIEW viewtest AS
59 SELECT a, b::numeric FROM viewtest_tbl;
60 ERROR: cannot change data type of view column "b" from integer to numeric
61 -- should work
62 CREATE OR REPLACE VIEW viewtest AS
63 SELECT a, b, 0 AS c FROM viewtest_tbl;
64 DROP VIEW viewtest;
65 DROP TABLE viewtest_tbl;
66 -- tests for temporary views
67 CREATE SCHEMA temp_view_test
68 CREATE TABLE base_table (a int, id int)
69 CREATE TABLE base_table2 (a int, id int);
70 SET search_path TO temp_view_test, public;
71 CREATE TEMPORARY TABLE temp_table (a int, id int);
72 -- should be created in temp_view_test schema
73 CREATE VIEW v1 AS SELECT * FROM base_table;
74 -- should be created in temp object schema
75 CREATE VIEW v1_temp AS SELECT * FROM temp_table;
76 NOTICE: view "v1_temp" will be a temporary view
77 -- should be created in temp object schema
78 CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
79 -- should be created in temp_views schema
80 CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
81 -- should fail
82 CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
83 NOTICE: view "v3_temp" will be a temporary view
84 ERROR: temporary tables cannot specify a schema name
85 -- should fail
86 CREATE SCHEMA test_schema
87 CREATE TEMP VIEW testview AS SELECT 1;
88 ERROR: temporary tables cannot specify a schema name
89 -- joins: if any of the join relations are temporary, the view
90 -- should also be temporary
91 -- should be non-temp
92 CREATE VIEW v3 AS
93 SELECT t1.a AS t1_a, t2.a AS t2_a
94 FROM base_table t1, base_table2 t2
95 WHERE t1.id = t2.id;
96 -- should be temp (one join rel is temp)
97 CREATE VIEW v4_temp AS
98 SELECT t1.a AS t1_a, t2.a AS t2_a
99 FROM base_table t1, temp_table t2
100 WHERE t1.id = t2.id;
101 NOTICE: view "v4_temp" will be a temporary view
102 -- should be temp
103 CREATE VIEW v5_temp AS
104 SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a
105 FROM base_table t1, base_table2 t2, temp_table t3
106 WHERE t1.id = t2.id and t2.id = t3.id;
107 NOTICE: view "v5_temp" will be a temporary view
108 -- subqueries
109 CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
110 CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
111 CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2);
112 CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
113 CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
114 CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
115 NOTICE: view "v6_temp" will be a temporary view
116 CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
117 NOTICE: view "v7_temp" will be a temporary view
118 CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
119 NOTICE: view "v8_temp" will be a temporary view
120 CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
121 NOTICE: view "v9_temp" will be a temporary view
122 -- a view should also be temporary if it references a temporary view
123 CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
124 NOTICE: view "v10_temp" will be a temporary view
125 CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
126 NOTICE: view "v11_temp" will be a temporary view
127 CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
128 NOTICE: view "v12_temp" will be a temporary view
129 -- a view should also be temporary if it references a temporary sequence
130 CREATE SEQUENCE seq1;
131 CREATE TEMPORARY SEQUENCE seq1_temp;
132 CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1;
133 CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp;
134 NOTICE: view "v13_temp" will be a temporary view
135 SELECT relname FROM pg_class
136 WHERE relname LIKE 'v_'
137 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
138 ORDER BY relname;
139 relname
140 ---------
141 v1
142 v2
143 v3
144 v4
145 v5
146 v6
147 v7
148 v8
149 v9
150 (9 rows)
152 SELECT relname FROM pg_class
153 WHERE relname LIKE 'v%'
154 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
155 ORDER BY relname;
156 relname
157 ----------
158 v10_temp
159 v11_temp
160 v12_temp
161 v13_temp
162 v1_temp
163 v2_temp
164 v4_temp
165 v5_temp
166 v6_temp
167 v7_temp
168 v8_temp
169 v9_temp
170 (12 rows)
172 CREATE SCHEMA testviewschm2;
173 SET search_path TO testviewschm2, public;
174 CREATE TABLE t1 (num int, name text);
175 CREATE TABLE t2 (num2 int, value text);
176 CREATE TEMP TABLE tt (num2 int, value text);
177 CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2;
178 CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt;
179 NOTICE: view "temporal1" will be a temporary view
180 CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2;
181 CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2;
182 NOTICE: view "temporal2" will be a temporary view
183 CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
184 CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
185 NOTICE: view "temporal3" will be a temporary view
186 CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
187 CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
188 NOTICE: view "temporal4" will be a temporary view
189 SELECT relname FROM pg_class
190 WHERE relname LIKE 'nontemp%'
191 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2')
192 ORDER BY relname;
193 relname
194 ----------
195 nontemp1
196 nontemp2
197 nontemp3
198 nontemp4
199 (4 rows)
201 SELECT relname FROM pg_class
202 WHERE relname LIKE 'temporal%'
203 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
204 ORDER BY relname;
205 relname
206 -----------
207 temporal1
208 temporal2
209 temporal3
210 temporal4
211 (4 rows)
213 CREATE TABLE tbl1 ( a int, b int);
214 CREATE TABLE tbl2 (c int, d int);
215 CREATE TABLE tbl3 (e int, f int);
216 CREATE TABLE tbl4 (g int, h int);
217 CREATE TEMP TABLE tmptbl (i int, j int);
218 --Should be in testviewschm2
219 CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a
220 BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
221 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f);
222 SELECT count(*) FROM pg_class where relname = 'pubview'
223 AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2');
224 count
225 -------
226 1
227 (1 row)
229 --Should be in temp object schema
230 CREATE VIEW mytempview AS SELECT * FROM tbl1 WHERE tbl1.a
231 BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
232 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
233 AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
234 NOTICE: view "mytempview" will be a temporary view
235 SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
236 And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
237 count
238 -------
239 1
240 (1 row)
242 DROP SCHEMA temp_view_test CASCADE;
243 NOTICE: drop cascades to 22 other objects
244 DETAIL: drop cascades to table temp_view_test.base_table
245 drop cascades to view v7_temp
246 drop cascades to view v10_temp
247 drop cascades to view v11_temp
248 drop cascades to view v12_temp
249 drop cascades to view v2_temp
250 drop cascades to view v4_temp
251 drop cascades to view v6_temp
252 drop cascades to view v8_temp
253 drop cascades to view v9_temp
254 drop cascades to table temp_view_test.base_table2
255 drop cascades to view v5_temp
256 drop cascades to view temp_view_test.v1
257 drop cascades to view temp_view_test.v2
258 drop cascades to view temp_view_test.v3
259 drop cascades to view temp_view_test.v4
260 drop cascades to view temp_view_test.v5
261 drop cascades to view temp_view_test.v6
262 drop cascades to view temp_view_test.v7
263 drop cascades to view temp_view_test.v8
264 drop cascades to sequence temp_view_test.seq1
265 drop cascades to view temp_view_test.v9
266 DROP SCHEMA testviewschm2 CASCADE;
267 NOTICE: drop cascades to 16 other objects
268 DETAIL: drop cascades to table t1
269 drop cascades to view temporal1
270 drop cascades to view temporal2
271 drop cascades to view temporal3
272 drop cascades to view temporal4
273 drop cascades to table t2
274 drop cascades to view nontemp1
275 drop cascades to view nontemp2
276 drop cascades to view nontemp3
277 drop cascades to view nontemp4
278 drop cascades to table tbl1
279 drop cascades to table tbl2
280 drop cascades to table tbl3
281 drop cascades to table tbl4
282 drop cascades to view mytempview
283 drop cascades to view pubview
284 SET search_path to public;