--
-- CREATE_VIEW1
-- Virtual class definitions
--	(this also tests the query rewrite system)
--
-- Enforce use of COMMIT instead of 2PC for temporary objects
\c table_name_test_db;
set dolphin.lower_case_table_names TO 0;
CREATE VIEW street AS
   SELECT r.name, r.thepath, c.cname AS cname
   FROM ONLY (Road) r, Real_City c
   WHERE c.outline ## r.thepath;
ERROR:  argument of WHERE must be type boolean, not type path
LINE 4:    WHERE c.outline ## r.thepath;
                 ^
CREATE VIEW iexit AS
   SELECT ih.name, ih.thepath,
	interpt_pp(ih.thepath, r.thepath) AS exit
   FROM iHighway ih, ramp r
   WHERE ih.thepath ## r.thepath;
ERROR:  relation "ramp" does not exist on datanode1
LINE 4:    FROM iHighway ih, ramp r
                             ^
CREATE VIEW toyemp AS
   SELECT name, age, location, 12*salary AS annualsal
   FROM Emp;
-- Test comments
COMMENT ON VIEW noview IS 'no view';
ERROR:  relation "noview" does not exist
COMMENT ON VIEW toyemp IS 'is a view';
COMMENT ON VIEW toyemp IS NULL;
--
-- CREATE OR REPLACE VIEW
--
CREATE TABLE viewtest_tbl (a int, b int);
COPY viewtest_tbl FROM stdin;
CREATE OR REPLACE VIEW viewtest AS
	SELECT * FROM viewtest_tbl;
CREATE OR REPLACE VIEW viewtest AS
	SELECT * FROM viewtest_tbl WHERE a > 10;
SELECT * FROM viewtest ORDER BY a;
 a  | b  
----+----
 15 | 20
 20 | 25
(2 rows)

CREATE OR REPLACE VIEW viewtest AS
	SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
SELECT * FROM viewtest ORDER BY a;
 a  | b  
----+----
 10 | 15
 15 | 20
 20 | 25
(3 rows)

-- should fail
CREATE OR REPLACE VIEW viewtest AS
	SELECT a FROM viewtest_tbl WHERE a <> 20;
ERROR:  cannot drop columns from view
-- should fail
set dolphin.use_const_value_as_colname = false;
CREATE OR REPLACE VIEW viewtest AS
	SELECT 1, * FROM viewtest_tbl;
ERROR:  cannot change name of view column "a" to "?column?"
-- should fail
CREATE OR REPLACE VIEW viewtest AS
	SELECT a, b::numeric FROM viewtest_tbl;
ERROR:  cannot change data type of view column "b" from integer to numeric(10,0)
-- should work
CREATE OR REPLACE VIEW viewtest AS
	SELECT a, b, 0 AS c FROM viewtest_tbl;
DROP SCHEMA IF EXISTS test_schema1 CASCADE;
NOTICE:  schema "test_schema1" does not exist, skipping
CREATE SCHEMA test_schema1;
CREATE OPERATOR test_schema1.-(rightarg = int1, procedure = int1um);
CREATE OPERATOR test_schema1.~(leftarg = int1, procedure = int1up);
set current_schema to 'test_schema1';
CREATE VIEW test as select -(1::tinyint) as "arg1", (1::tinyint)~ as "arg2";
ERROR:  syntax error at or near "as"
LINE 1: ... as select -(1::tinyint) as "arg1", (1::tinyint)~ as "arg2";
                                                             ^
SELECT * FROM test;
ERROR:  relation "test" does not exist on datanode1
LINE 1: SELECT * FROM test;
                      ^
RESET current_schema;
DROP SCHEMA IF EXISTS test_schema1 CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to operator test_schema1.-(NONE,tinyint)
drop cascades to operator test_schema1.~(tinyint,NONE)
-- test error message
DROP TABLE viewtest;
ERROR:  "viewtest" is not a table
HINT:  Use DROP VIEW to remove a view.
DROP VIEW viewtest;
DROP TABLE viewtest_tbl;