src/test/regress/sql/create_view.sql

   1 --
   2 -- CREATE_VIEW
   3 -- Virtual class definitions
   4 --      (this also tests the query rewrite system)
   5 --
   6
   7 CREATE VIEW street AS
   8    SELECT r.name, r.thepath, c.cname AS cname
   9    FROM ONLY road r, real_city c
  10    WHERE c.outline ## r.thepath;
  11
  12 CREATE VIEW iexit AS
  13    SELECT ih.name, ih.thepath,
  14         interpt_pp(ih.thepath, r.thepath) AS exit
  15    FROM ihighway ih, ramp r
  16    WHERE ih.thepath ## r.thepath;
  17
  18 CREATE VIEW toyemp AS
  19    SELECT name, age, location, 12*salary AS annualsal
  20    FROM emp;
  21
  22 -- Test comments
  23 COMMENT ON VIEW noview IS 'no view';
  24 COMMENT ON VIEW toyemp IS 'is a view';
  25 COMMENT ON VIEW toyemp IS NULL;
  26
  27 --
  28 -- CREATE OR REPLACE VIEW
  29 --
  30
  31 CREATE TABLE viewtest_tbl (a int, b int);
  32 COPY viewtest_tbl FROM stdin;
  33 5       10
  34 10      15
  35 15      20
  36 20      25
  37 \.
  38
  39 CREATE OR REPLACE VIEW viewtest AS
  40         SELECT * FROM viewtest_tbl;
  41
  42 CREATE OR REPLACE VIEW viewtest AS
  43         SELECT * FROM viewtest_tbl WHERE a > 10;
  44
  45 SELECT * FROM viewtest;
  46
  47 CREATE OR REPLACE VIEW viewtest AS
  48         SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
  49
  50 SELECT * FROM viewtest;
  51
  52 -- should fail
  53 CREATE OR REPLACE VIEW viewtest AS
  54         SELECT a FROM viewtest_tbl WHERE a <> 20;
  55
  56 -- should fail
  57 CREATE OR REPLACE VIEW viewtest AS
  58         SELECT 1, * FROM viewtest_tbl;
  59
  60 -- should fail
  61 CREATE OR REPLACE VIEW viewtest AS
  62         SELECT a, b::numeric FROM viewtest_tbl;
  63
  64 DROP VIEW viewtest;
  65 DROP TABLE viewtest_tbl;
  66
  67 -- tests for temporary views
  68
  69 CREATE SCHEMA temp_view_test
  70     CREATE TABLE base_table (a int, id int)
  71     CREATE TABLE base_table2 (a int, id int);
  72
  73 SET search_path TO temp_view_test, public;
  74
  75 CREATE TEMPORARY TABLE temp_table (a int, id int);
  76
  77 -- should be created in temp_view_test schema
  78 CREATE VIEW v1 AS SELECT * FROM base_table;
  79 -- should be created in temp object schema
  80 CREATE VIEW v1_temp AS SELECT * FROM temp_table;
  81 -- should be created in temp object schema
  82 CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
  83 -- should be created in temp_views schema
  84 CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
  85 -- should fail
  86 CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
  87 -- should fail
  88 CREATE SCHEMA test_schema
  89     CREATE TEMP VIEW testview AS SELECT 1;
  90
  91 -- joins: if any of the join relations are temporary, the view
  92 -- should also be temporary
  93
  94 -- should be non-temp
  95 CREATE VIEW v3 AS
  96     SELECT t1.a AS t1_a, t2.a AS t2_a
  97     FROM base_table t1, base_table2 t2
  98     WHERE t1.id = t2.id;
  99 -- should be temp (one join rel is temp)
 100 CREATE VIEW v4_temp AS
 101     SELECT t1.a AS t1_a, t2.a AS t2_a
 102     FROM base_table t1, temp_table t2
 103     WHERE t1.id = t2.id;
 104 -- should be temp
 105 CREATE VIEW v5_temp AS
 106     SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a
 107     FROM base_table t1, base_table2 t2, temp_table t3
 108     WHERE t1.id = t2.id and t2.id = t3.id;
 109
 110 -- subqueries
 111 CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
 112 CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
 113 CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2);
 114 CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
 115 CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
 116
 117 CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
 118 CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
 119 CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
 120 CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
 121
 122 -- a view should also be temporary if it references a temporary view
 123 CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
 124 CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
 125 CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
 126
 127 -- a view should also be temporary if it references a temporary sequence
 128 CREATE SEQUENCE seq1;
 129 CREATE TEMPORARY SEQUENCE seq1_temp;
 130 CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1;
 131 CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp;
 132
 133 SELECT relname FROM pg_class
 134     WHERE relname LIKE 'v_'
 135     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
 136     ORDER BY relname;
 137 SELECT relname FROM pg_class
 138     WHERE relname LIKE 'v%'
 139     AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
 140     ORDER BY relname;
 141
 142 CREATE SCHEMA testviewschm2;
 143 SET search_path TO testviewschm2, public;
 144
 145 CREATE TABLE t1 (num int, name text);
 146 CREATE TABLE t2 (num2 int, value text);
 147 CREATE TEMP TABLE tt (num2 int, value text);
 148
 149 CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2;
 150 CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt;
 151 CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2;
 152 CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2;
 153 CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
 154 CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
 155 CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
 156 CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
 157
 158 SELECT relname FROM pg_class
 159     WHERE relname LIKE 'nontemp%'
 160     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2')
 161     ORDER BY relname;
 162 SELECT relname FROM pg_class
 163     WHERE relname LIKE 'temporal%'
 164     AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
 165     ORDER BY relname;
 166
 167 CREATE TABLE tbl1 ( a int, b int);
 168 CREATE TABLE tbl2 (c int, d int);
 169 CREATE TABLE tbl3 (e int, f int);
 170 CREATE TABLE tbl4 (g int, h int);
 171 CREATE TEMP TABLE tmptbl (i int, j int);
 172
 173 --Should be in testviewschm2
 174 CREATE   VIEW  pubview AS SELECT * FROM tbl1 WHERE tbl1.a
 175 BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
 176 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f);
 177
 178 SELECT count(*) FROM pg_class where relname = 'pubview'
 179 AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2');
 180
 181 --Should be in temp object schema
 182 CREATE   VIEW  mytempview AS SELECT * FROM tbl1 WHERE tbl1.a
 183 BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
 184 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
 185 AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
 186
 187 SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
 188 And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
 189
 190 DROP SCHEMA temp_view_test CASCADE;
 191 DROP SCHEMA testviewschm2 CASCADE;
 192
 193 SET search_path to public;