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;