000001 hash-threshold 8 000002 000003 statement ok 000004 CREATE TABLE t1( x INTEGER, y VARCHAR(8) ) 000005 000006 statement ok 000007 INSERT INTO t1 VALUES(1,'true') 000008 000009 statement ok 000010 INSERT INTO t1 VALUES(0,'false') 000011 000012 statement ok 000013 INSERT INTO t1 VALUES(NULL,'NULL') 000014 000015 statement ok 000016 CREATE INDEX t1i1 ON t1(x) 000017 000018 000019 # TBD-EVIDENCE-OF: R-13439-14752 The CREATE VIEW command assigns a name to a 000020 # pre-packaged SELECT statement. 000021 000022 statement ok 000023 CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0 000024 000025 # already exists 000026 statement error 000027 CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0 000028 000029 query I rowsort label-0 000030 SELECT x FROM t1 WHERE x>0 000031 ---- 000032 1 000033 000034 query I rowsort label-0 000035 SELECT x FROM view1 000036 ---- 000037 1 000038 000039 # TBD-EVIDENCE-OF: R-63075-15970 Once the view is created, it can be used in 000040 # the FROM clause of another SELECT in place of a table name. 000041 000042 # PARTIAL-EVIDENCE-OF: R-48816-31606 If the "TEMP" or "TEMPORARY" keyword occurs 000043 # in between "CREATE" and "VIEW" then the view that is created is only 000044 # visible to the process that opened the database and is automatically 000045 # deleted when the database is closed. 000046 000047 onlyif sqlite 000048 statement ok 000049 CREATE TEMP VIEW view2 AS SELECT x FROM t1 WHERE x>0 000050 000051 onlyif sqlite 000052 statement ok 000053 CREATE TEMPORARY VIEW view3 AS SELECT x FROM t1 WHERE x>0 000054 000055 # TBD-EVIDENCE-OF: R-49139-10687 If a <database-name> is specified, 000056 # then the view is created in the named database. 000057 000058 # TBD-EVIDENCE-OF: R-48195-55336 It is an error to specify both a 000059 # <database-name> and the TEMP keyword on a VIEW, unless the 000060 # <database-name> is "temp". 000061 000062 # TBD-EVIDENCE-OF: R-49817-02954 If no database name is specified, and the 000063 # TEMP keyword is not present, the VIEW is created in the main database. 000064 000065 # EVIDENCE-OF: R-16775-34716 You cannot DELETE, INSERT, or UPDATE a 000066 # view. 000067 000068 skipif mssql # this is allowed 000069 statement error 000070 DELETE FROM view1 WHERE x>0 000071 000072 onlyif mssql # this is allowed 000073 statement ok 000074 DELETE FROM view1 WHERE x>0 000075 000076 statement error 000077 INSERT INTO view1 VALUES(2,'unknown') 000078 000079 skipif mssql # this is allowed 000080 statement error 000081 UPDATE view1 SET x=2 000082 000083 onlyif mssql # this is allowed 000084 statement ok 000085 UPDATE view1 SET x=2 000086 000087 # PARTIAL-EVIDENCE-OF: R-05363-17893 Views are read-only in SQLite. 000088 000089 onlyif sqlite 000090 statement error 000091 DELETE FROM view1 WHERE x>0 000092 000093 onlyif sqlite 000094 statement error 000095 INSERT INTO view1 VALUES(2,'unknown') 000096 000097 onlyif sqlite 000098 statement error 000099 INSERT OR REPLACE INTO view1 VALUES(2,'unknown') 000100 000101 onlyif sqlite 000102 statement error 000103 UPDATE view1 SET x=2 000104 000105 # TBD-EVIDENCE-OF: R-16876-26469 However, in many cases you can use an 000106 # INSTEAD OF trigger on the view to accomplish the same thing. 000107 000108 # EVIDENCE-OF: R-10484-47921 Views are removed with the DROP VIEW 000109 # command. 000110 000111 statement ok 000112 DROP VIEW view1 000113 000114 onlyif sqlite 000115 statement ok 000116 DROP VIEW view2 000117 000118 onlyif sqlite 000119 statement ok 000120 DROP VIEW view3 000121 000122 # already dropped 000123 statement error 000124 DROP VIEW view1 000125 000126 # never existed 000127 statement error 000128 DROP VIEW viewX