--
-- CREATE_VIEW
-- Virtual class definitions
-- (this also tests the query rewrite system)
--
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;
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;
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;
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;
a | b
----+----
20 | 25
15 | 20
10 | 15
(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
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
-- should work
CREATE OR REPLACE VIEW viewtest AS
SELECT a, b, 0 AS c FROM viewtest_tbl;
DROP VIEW viewtest;
DROP TABLE viewtest_tbl;
-- tests for temporary views
CREATE SCHEMA temp_view_test
CREATE TABLE base_table (a int, id int)
CREATE TABLE base_table2 (a int, id int);
SET search_path TO temp_view_test, public;
CREATE TEMPORARY TABLE temp_table (a int, id int);
-- should be created in temp_view_test schema
CREATE VIEW v1 AS SELECT * FROM base_table;
-- should be created in temp object schema
CREATE VIEW v1_temp AS SELECT * FROM temp_table;
NOTICE: view "v1_temp" will be a temporary view
-- should be created in temp object schema
CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
-- should be created in temp_views schema
CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
-- should fail
CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
NOTICE: view "v3_temp" will be a temporary view
ERROR: cannot create temporary relation in non-temporary schema
-- should fail
CREATE SCHEMA test_schema
CREATE TEMP VIEW testview AS SELECT 1;
ERROR: cannot create temporary relation in non-temporary schema
-- joins: if any of the join relations are temporary, the view
-- should also be temporary
-- should be non-temp
CREATE VIEW v3 AS
SELECT t1.a AS t1_a, t2.a AS t2_a
FROM base_table t1, base_table2 t2
WHERE t1.id = t2.id;
-- should be temp (one join rel is temp)
CREATE VIEW v4_temp AS
SELECT t1.a AS t1_a, t2.a AS t2_a
FROM base_table t1, temp_table t2
WHERE t1.id = t2.id;
NOTICE: view "v4_temp" will be a temporary view
-- should be temp
CREATE VIEW v5_temp AS
SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a
FROM base_table t1, base_table2 t2, temp_table t3
WHERE t1.id = t2.id and t2.id = t3.id;
NOTICE: view "v5_temp" will be a temporary view
-- subqueries
CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2);
CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
NOTICE: view "v6_temp" will be a temporary view
CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
NOTICE: view "v7_temp" will be a temporary view
CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
NOTICE: view "v8_temp" will be a temporary view
CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
NOTICE: view "v9_temp" will be a temporary view
-- a view should also be temporary if it references a temporary view
CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
NOTICE: view "v10_temp" will be a temporary view
CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
NOTICE: view "v11_temp" will be a temporary view
CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
NOTICE: view "v12_temp" will be a temporary view
-- a view should also be temporary if it references a temporary sequence
CREATE SEQUENCE seq1;
CREATE TEMPORARY SEQUENCE seq1_temp;
CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1;
CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp;
NOTICE: view "v13_temp" will be a temporary view
SELECT relname FROM pg_class
WHERE relname LIKE 'v_'
AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
ORDER BY relname;
relname
---------
v1
v2
v3
v4
v5
v6
v7
v8
v9
(9 rows)
SELECT relname FROM pg_class
WHERE relname LIKE 'v%'
AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
ORDER BY relname;
relname
----------
v10_temp
v11_temp
v12_temp
v13_temp
v1_temp
v2_temp
v4_temp
v5_temp
v6_temp
v7_temp
v8_temp
v9_temp
(12 rows)
CREATE SCHEMA testviewschm2;
SET search_path TO testviewschm2, public;
CREATE TABLE t1 (num int, name text);
CREATE TABLE t2 (num2 int, value text);
CREATE TEMP TABLE tt (num2 int, value text);
CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2;
CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt;
NOTICE: view "temporal1" will be a temporary view
CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2;
CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2;
NOTICE: view "temporal2" will be a temporary view
CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
NOTICE: view "temporal3" will be a temporary view
CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
NOTICE: view "temporal4" will be a temporary view
SELECT relname FROM pg_class
WHERE relname LIKE 'nontemp%'
AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2')
ORDER BY relname;
relname
----------
nontemp1
nontemp2
nontemp3
nontemp4
(4 rows)
SELECT relname FROM pg_class
WHERE relname LIKE 'temporal%'
AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
ORDER BY relname;
relname
-----------
temporal1
temporal2
temporal3
temporal4
(4 rows)
CREATE TABLE tbl1 ( a int, b int);
CREATE TABLE tbl2 (c int, d int);
CREATE TABLE tbl3 (e int, f int);
CREATE TABLE tbl4 (g int, h int);
CREATE TEMP TABLE tmptbl (i int, j int);
--Should be in testviewschm2
CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a
BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f);
SELECT count(*) FROM pg_class where relname = 'pubview'
AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2');
count
-------
1
(1 row)
--Should be in temp object schema
CREATE VIEW mytempview AS SELECT * FROM tbl1 WHERE tbl1.a
BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
NOTICE: view "mytempview" will be a temporary view
SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
count
-------
1
(1 row)
--
-- CREATE VIEW and WITH(...) clause
--
CREATE VIEW mysecview1
AS SELECT * FROM tbl1 WHERE a = 0;
CREATE VIEW mysecview2 WITH (security_barrier=true)
AS SELECT * FROM tbl1 WHERE a > 0;
CREATE VIEW mysecview3 WITH (security_barrier=false)
AS SELECT * FROM tbl1 WHERE a < 0;
CREATE VIEW mysecview4 WITH (security_barrier)
AS SELECT * FROM tbl1 WHERE a <> 0;
CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error
AS SELECT * FROM tbl1 WHERE a > 100;
ERROR: invalid value for boolean option "security_barrier": 100
CREATE VIEW mysecview6 WITH (invalid_option) -- Error
AS SELECT * FROM tbl1 WHERE a < 100;
ERROR: unrecognized parameter "invalid_option"
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
'mysecview3'::regclass, 'mysecview4'::regclass)
ORDER BY relname;
relname | relkind | reloptions
------------+---------+--------------------------
mysecview1 | v |
mysecview2 | v | {security_barrier=true}
mysecview3 | v | {security_barrier=false}
mysecview4 | v | {security_barrier=true}
(4 rows)
CREATE OR REPLACE VIEW mysecview1
AS SELECT * FROM tbl1 WHERE a = 256;
CREATE OR REPLACE VIEW mysecview2
AS SELECT * FROM tbl1 WHERE a > 256;
CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true)
AS SELECT * FROM tbl1 WHERE a < 256;
CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false)
AS SELECT * FROM tbl1 WHERE a <> 256;
SELECT relname, relkind, reloptions FROM pg_class
WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
'mysecview3'::regclass, 'mysecview4'::regclass)
ORDER BY relname;
relname | relkind | reloptions
------------+---------+--------------------------
mysecview1 | v |
mysecview2 | v |
mysecview3 | v | {security_barrier=true}
mysecview4 | v | {security_barrier=false}
(4 rows)
-- This test checks that proper typmods are assigned in a multi-row VALUES
CREATE VIEW tt1 AS
SELECT * FROM (
VALUES
('abc'::varchar(3), '0123456789', 42, 'abcd'::varchar(4)),
('0123456789', 'abc'::varchar(3), 42.12, 'abc'::varchar(4))
) vv(a,b,c,d);
\d+ tt1
View "testviewschm2.tt1"
Column | Type | Modifiers | Storage | Description
--------+----------------------+-----------+----------+-------------
a | character varying | | extended |
b | character varying | | extended |
c | numeric | | main |
d | character varying(4) | | extended |
View definition:
SELECT vv.a, vv.b, vv.c, vv.d
FROM ( VALUES ('abc'::character varying(3),'0123456789'::character varying,42,'abcd'::character varying(4)), ('0123456789'::character varying,'abc'::character varying(3),42.12,'abc'::character varying(4))) vv(a, b, c, d);
SELECT * FROM tt1;
a | b | c | d
------------+------------+-------+------
abc | 0123456789 | 42 | abcd
0123456789 | abc | 42.12 | abc
(2 rows)
SELECT a::varchar(3) FROM tt1;
a
-----
abc
012
(2 rows)
DROP VIEW tt1;
-- check display of whole-row variables in some corner cases
create type nestedcomposite as (x int8_tbl);
create view tt15v as select row(i)::nestedcomposite from int8_tbl i;
select * from tt15v;
row
------------------------------------------
("(123,456)")
("(123,4567890123456789)")
("(4567890123456789,123)")
("(4567890123456789,4567890123456789)")
("(4567890123456789,-4567890123456789)")
(5 rows)
select pg_get_viewdef('tt15v', true);
pg_get_viewdef
------------------------------------------------------
SELECT ROW(i.*::int8_tbl)::nestedcomposite AS "row"+
FROM int8_tbl i;
(1 row)
select row(i.*::int8_tbl)::nestedcomposite from int8_tbl i;
row
------------------------------------------
("(123,456)")
("(123,4567890123456789)")
("(4567890123456789,123)")
("(4567890123456789,4567890123456789)")
("(4567890123456789,-4567890123456789)")
(5 rows)
create view tt17v as select * from int8_tbl i where i in (values(i));
select * from tt17v;
q1 | q2
------------------+-------------------
123 | 456
123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(5 rows)
select pg_get_viewdef('tt17v', true);
pg_get_viewdef
---------------------------------------------
SELECT i.q1, i.q2 +
FROM int8_tbl i +
WHERE (i.* IN ( VALUES (i.*::int8_tbl)));
(1 row)
select * from int8_tbl i where i.* in (values(i.*::int8_tbl));
q1 | q2
------------------+-------------------
123 | 456
123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(5 rows)
-- check display of ScalarArrayOp with a sub-select
select 'foo'::text = any(array['abc','def','foo']::text[]);
?column?
----------
t
(1 row)
select 'foo'::text = any((select array['abc','def','foo']::text[])); -- fail
ERROR: operator does not exist: text = text[]
LINE 1: select 'foo'::text = any((select array['abc','def','foo']::t...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
select 'foo'::text = any((select array['abc','def','foo']::text[])::text[]);
?column?
----------
t
(1 row)
create view tt19v as
select 'foo'::text = any(array['abc','def','foo']::text[]) c1,
'foo'::text = any((select array['abc','def','foo']::text[])::text[]) c2;
select pg_get_viewdef('tt19v', true);
pg_get_viewdef
------------------------------------------------------------------------------------------------------------
SELECT 'foo'::text = ANY (ARRAY['abc'::text, 'def'::text, 'foo'::text]) AS c1, +
'foo'::text = ANY ((( SELECT ARRAY['abc'::text, 'def'::text, 'foo'::text] AS "array"))::text[]) AS c2;
(1 row)
-- check display of assorted RTE_FUNCTION expressions
create view tt20v as
select * from
coalesce(1,2) as c,
collation for ('x'::text) col,
current_date as d,
cast(1+2 as int4) as i4,
cast(1+2 as int8) as i8;
select pg_get_viewdef('tt20v', true);
pg_get_viewdef
---------------------------------------------------------------------------
SELECT c.c, col.col, d.d, i4.i4, i8.i8 +
FROM COALESCE(1, 2) c(c), pg_collation_for('x'::text) col(col), +
CAST('now'::text::date AS date) d(d), CAST(1 + 2 AS integer) i4(i4), +
CAST((1 + 2)::bigint AS bigint) i8(i8);
(1 row)
-- check handling of views with immediately-renamed columns
create view tt23v (col_a, col_b) as
select q1 as other_name1, q2 as other_name2 from int8_tbl
union
select 42, 43;
select pg_get_viewdef('tt23v', true);
pg_get_viewdef
------------------------------------------------------------
SELECT int8_tbl.q1 AS col_a, int8_tbl.q2 AS col_b+
FROM int8_tbl +
UNION +
SELECT 42 AS col_a, 43 AS col_b;
(1 row)
select pg_get_ruledef(oid, true) from pg_rewrite
where ev_class = 'tt23v'::regclass and ev_type = '1';
pg_get_ruledef
--------------------------------------------------------------------------
CREATE RULE "_RETURN" AS +
ON SELECT TO tt23v DO INSTEAD SELECT int8_tbl.q1 AS col_a, +
int8_tbl.q2 AS col_b +
FROM int8_tbl +
UNION +
SELECT 42 AS col_a, 43 AS col_b;
(1 row)
-- clean up all the random objects we made above
set client_min_messages = warning;
DROP SCHEMA temp_view_test CASCADE;
DROP SCHEMA testviewschm2 CASCADE;
SET search_path to public;