3 -- Virtual class definitions
4 -- (this also tests the query rewrite system)
7 SELECT r.name, r.thepath, c.cname AS cname
8 FROM ONLY road r, real_city c
9 WHERE c.outline ## r.thepath;
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;
16 SELECT name, age, location, 12*salary AS annualsal
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;
24 -- CREATE OR REPLACE VIEW
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;
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;
50 CREATE OR REPLACE VIEW viewtest AS
51 SELECT a FROM viewtest_tbl WHERE a <> 20;
52 ERROR: cannot change number of columns in view
54 CREATE OR REPLACE VIEW viewtest AS
55 SELECT 1, * FROM viewtest_tbl;
56 ERROR: cannot change number of columns in view
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"
62 DROP TABLE viewtest_tbl;
63 -- tests for temporary views
64 CREATE SCHEMA temp_view_test
65 CREATE TABLE base_table (a int, id int)
66 CREATE TABLE base_table2 (a int, id int);
67 SET search_path TO temp_view_test, public;
68 CREATE TEMPORARY TABLE temp_table (a int, id int);
69 -- should be created in temp_view_test schema
70 CREATE VIEW v1 AS SELECT * FROM base_table;
71 -- should be created in temp object schema
72 CREATE VIEW v1_temp AS SELECT * FROM temp_table;
73 NOTICE: view "v1_temp" will be a temporary view
74 -- should be created in temp object schema
75 CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
76 -- should be created in temp_views schema
77 CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
79 CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
80 NOTICE: view "v3_temp" will be a temporary view
81 ERROR: temporary tables cannot specify a schema name
83 CREATE SCHEMA test_schema
84 CREATE TEMP VIEW testview AS SELECT 1;
85 ERROR: temporary tables cannot specify a schema name
86 -- joins: if any of the join relations are temporary, the view
87 -- should also be temporary
90 SELECT t1.a AS t1_a, t2.a AS t2_a
91 FROM base_table t1, base_table2 t2
93 -- should be temp (one join rel is temp)
94 CREATE VIEW v4_temp AS
95 SELECT t1.a AS t1_a, t2.a AS t2_a
96 FROM base_table t1, temp_table t2
98 NOTICE: view "v4_temp" will be a temporary view
100 CREATE VIEW v5_temp AS
101 SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a
102 FROM base_table t1, base_table2 t2, temp_table t3
103 WHERE t1.id = t2.id and t2.id = t3.id;
104 NOTICE: view "v5_temp" will be a temporary view
106 CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
107 CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
108 CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2);
109 CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
110 CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
111 CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
112 NOTICE: view "v6_temp" will be a temporary view
113 CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
114 NOTICE: view "v7_temp" will be a temporary view
115 CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
116 NOTICE: view "v8_temp" will be a temporary view
117 CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
118 NOTICE: view "v9_temp" will be a temporary view
119 -- a view should also be temporary if it references a temporary view
120 CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
121 NOTICE: view "v10_temp" will be a temporary view
122 CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
123 NOTICE: view "v11_temp" will be a temporary view
124 CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
125 NOTICE: view "v12_temp" will be a temporary view
126 -- a view should also be temporary if it references a temporary sequence
127 CREATE SEQUENCE seq1;
128 CREATE TEMPORARY SEQUENCE seq1_temp;
129 CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1;
130 CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp;
131 NOTICE: view "v13_temp" will be a temporary view
132 SELECT relname FROM pg_class
133 WHERE relname LIKE 'v_'
134 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
149 SELECT relname FROM pg_class
150 WHERE relname LIKE 'v%'
151 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
169 CREATE SCHEMA testviewschm2;
170 SET search_path TO testviewschm2, public;
171 CREATE TABLE t1 (num int, name text);
172 CREATE TABLE t2 (num2 int, value text);
173 CREATE TEMP TABLE tt (num2 int, value text);
174 CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2;
175 CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt;
176 NOTICE: view "temporal1" will be a temporary view
177 CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2;
178 CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2;
179 NOTICE: view "temporal2" will be a temporary view
180 CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
181 CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
182 NOTICE: view "temporal3" will be a temporary view
183 CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
184 CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
185 NOTICE: view "temporal4" will be a temporary view
186 SELECT relname FROM pg_class
187 WHERE relname LIKE 'nontemp%'
188 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2')
198 SELECT relname FROM pg_class
199 WHERE relname LIKE 'temporal%'
200 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
210 CREATE TABLE tbl1 ( a int, b int);
211 CREATE TABLE tbl2 (c int, d int);
212 CREATE TABLE tbl3 (e int, f int);
213 CREATE TABLE tbl4 (g int, h int);
214 CREATE TEMP TABLE tmptbl (i int, j int);
215 --Should be in testviewschm2
216 CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a
217 BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
218 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f);
219 SELECT count(*) FROM pg_class where relname = 'pubview'
220 AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2');
226 --Should be in temp object schema
227 CREATE VIEW mytempview AS SELECT * FROM tbl1 WHERE tbl1.a
228 BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
229 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
230 AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
231 NOTICE: view "mytempview" will be a temporary view
232 SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
233 And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
239 DROP SCHEMA temp_view_test CASCADE;
240 NOTICE: drop cascades to 22 other objects
241 DETAIL: drop cascades to table temp_view_test.base_table
242 drop cascades to view v7_temp
243 drop cascades to view v10_temp
244 drop cascades to view v11_temp
245 drop cascades to view v12_temp
246 drop cascades to view v2_temp
247 drop cascades to view v4_temp
248 drop cascades to view v6_temp
249 drop cascades to view v8_temp
250 drop cascades to view v9_temp
251 drop cascades to table temp_view_test.base_table2
252 drop cascades to view v5_temp
253 drop cascades to view temp_view_test.v1
254 drop cascades to view temp_view_test.v2
255 drop cascades to view temp_view_test.v3
256 drop cascades to view temp_view_test.v4
257 drop cascades to view temp_view_test.v5
258 drop cascades to view temp_view_test.v6
259 drop cascades to view temp_view_test.v7
260 drop cascades to view temp_view_test.v8
261 drop cascades to sequence temp_view_test.seq1
262 drop cascades to view temp_view_test.v9
263 DROP SCHEMA testviewschm2 CASCADE;
264 NOTICE: drop cascades to 16 other objects
265 DETAIL: drop cascades to table t1
266 drop cascades to view temporal1
267 drop cascades to view temporal2
268 drop cascades to view temporal3
269 drop cascades to view temporal4
270 drop cascades to table t2
271 drop cascades to view nontemp1
272 drop cascades to view nontemp2
273 drop cascades to view nontemp3
274 drop cascades to view nontemp4
275 drop cascades to table tbl1
276 drop cascades to table tbl2
277 drop cascades to table tbl3
278 drop cascades to table tbl4
279 drop cascades to view mytempview
280 drop cascades to view pubview
281 SET search_path to public;