--
-- CREATE_VIEW3
--
-- Enforce use of COMMIT instead of 2PC for temporary objects
--test COMMENT ON view's column
\c table_name_test_db;
set dolphin.lower_case_table_names TO 0;
set b_format_behavior_compat_options = '';
create table test_comment_normal_view_column_t(id1 int,id2 int);
create or replace view test_comment_normal_view_column_v as select * from test_comment_normal_view_column_t;
create temp table test_comment_temp_view_column_t(id1 int,id2 int);
create or replace temp view test_comment_temp_view_column_v as select * from test_comment_temp_view_column_t;
comment on column test_comment_normal_view_column_t.id1 is 'this is normal table';
comment on column test_comment_normal_view_column_v.id1 is 'this is normal view';
comment on column test_comment_temp_view_column_t.id1 is 'this is temp table';
comment on column test_comment_temp_view_column_v.id1 is 'this is temp view';
\d+ test_comment_normal_view_column_t
Table "public.test_comment_normal_view_column_t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+----------------------
id1 | integer | | plain | | this is normal table
id2 | integer | | plain | |
Has OIDs: no
Options: orientation=row, compression=no
\d+ test_comment_normal_view_column_v
View "public.test_comment_normal_view_column_v"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+---------------------
id1 | integer | | plain | this is normal view
id2 | integer | | plain |
View definition:
SELECT *
FROM test_comment_normal_view_column_t;
\d+ test_comment_temp_view_column_t
--?.*
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+--------------------
id1 | integer | | plain | | this is temp table
id2 | integer | | plain | |
Has OIDs: no
Options: orientation=row, compression=no
\d+ test_comment_temp_view_column_v
--?.*
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------------
id1 | integer | | plain | this is temp view
id2 | integer | | plain |
View definition:
SELECT *
FROM test_comment_temp_view_column_t;
comment on column test_comment_normal_view_column_t.id1 is 'this is normal table too';
comment on column test_comment_normal_view_column_v.id1 is 'this is normal view too';
comment on column test_comment_temp_view_column_t.id1 is 'this is temp table too';
comment on column test_comment_temp_view_column_v.id1 is 'this is temp view too';
\d+ test_comment_normal_view_column_t
Table "public.test_comment_normal_view_column_t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+--------------------------
id1 | integer | | plain | | this is normal table too
id2 | integer | | plain | |
Has OIDs: no
Options: orientation=row, compression=no
\d+ test_comment_normal_view_column_v
View "public.test_comment_normal_view_column_v"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------------------
id1 | integer | | plain | this is normal view too
id2 | integer | | plain |
View definition:
SELECT *
FROM test_comment_normal_view_column_t;
\d+ test_comment_temp_view_column_t
--?.*
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+------------------------
id1 | integer | | plain | | this is temp table too
id2 | integer | | plain | |
Has OIDs: no
Options: orientation=row, compression=no
\d+ test_comment_temp_view_column_v
--?.*
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-----------------------
id1 | integer | | plain | this is temp view too
id2 | integer | | plain |
View definition:
SELECT *
FROM test_comment_temp_view_column_t;
drop view test_comment_normal_view_column_v;
drop table test_comment_normal_view_column_t;
-- 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)
drop view tt19v;
-- 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))
) as vv(a,b,c,d);
\d+ tt1
View "public.tt1"
Column | Type | Modifiers | Storage | Description
--------+----------------------+-----------+----------+-------------
a | character varying | | extended |
b | character varying | | extended |
c | number | | main |
d | character varying(4) | | extended |
View definition:
SELECT *
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))) AS 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;
WARNING: value too long for type character varying(3)
CONTEXT: referenced column: a
a
-----
abc
012
(2 rows)
DROP VIEW tt1;
-- 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;
ERROR: relation "int8_tbl" does not exist on datanode1
LINE 2: select q1 as other_name1, q2 as other_name2 from int8_tbl
^
select pg_get_viewdef('tt23v', true);
ERROR: relation "tt23v" does not exist
CONTEXT: referenced column: pg_get_viewdef
select pg_get_ruledef(oid, true) from pg_rewrite
where ev_class = 'tt23v'::regclass and ev_type = '1';
ERROR: relation "tt23v" does not exist
LINE 2: where ev_class = 'tt23v'::regclass and ev_type = '1';
^
DROP VIEW tt23v;
ERROR: view "tt23v" does not exist
-- check display of assorted RTE_FUNCTION expressions
create view tt20v as
select * from
coalesce(1,2) as c,
pg_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 * +
FROM COALESCE(1, 2) AS c(c), pg_collation_for('x'::text) AS col(col), +
text_date('now'::text) AS d(d), +
CAST((1::bigint OPERATOR(dolphin_catalog.+) 2)::integer AS integer) AS i4(i4), +
CAST(1::bigint OPERATOR(dolphin_catalog.+) 2 AS bigint) AS i8(i8);
(1 row)
drop view tt20v;