set dolphin.lower_case_table_names=1;
create schema show_create;
set current_schema to 'show_create';
CREATE USER test_showcreate WITH PASSWORD 'openGauss@123';
GRANT ALL PRIVILEGES TO test_showcreate;
SET ROLE test_showcreate PASSWORD 'openGauss@123';
set dolphin.sql_mode = 'sql_mode_strict,sql_mode_full_group';
set b_format_behavior_compat_options = '';
create schema test_get_def;
set current_schema=test_get_def;
-- tests for table
create table table_function_export_def_base (
id integer primary key,
name varchar(100)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "table_function_export_def_base_pkey" for table "table_function_export_def_base"
create table table_function_export_def (
id integer primary key,
fid integer,
constraint table_export_base_fkey foreign key (fid) references table_function_export_def_base(id)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "table_function_export_def_pkey" for table "table_function_export_def"
show create table table_function_export_def_base;
Table | Create Table
--------------------------------+------------------------------------------------------------------------------------------------------------------------------
table_function_export_def_base | SET search_path = test_get_def; +
| CREATE TABLE table_function_export_def_base ( +
| id integer NOT NULL, +
| name character varying(100) +
| ) +
| WITH (orientation=row, compression=no); +
| ALTER TABLE table_function_export_def_base ADD CONSTRAINT table_function_export_def_base_pkey PRIMARY KEY USING btree (id);
(1 row)
show create table test_get_def.table_function_export_def_base;
Table | Create Table
--------------------------------+------------------------------------------------------------------------------------------------------------------------------
table_function_export_def_base | SET search_path = test_get_def; +
| CREATE TABLE table_function_export_def_base ( +
| id integer NOT NULL, +
| name character varying(100) +
| ) +
| WITH (orientation=row, compression=no); +
| ALTER TABLE table_function_export_def_base ADD CONSTRAINT table_function_export_def_base_pkey PRIMARY KEY USING btree (id);
(1 row)
show create table table_function_export_def;
Table | Create Table
---------------------------+--------------------------------------------------------------------------------------------------------------------
table_function_export_def | SET search_path = test_get_def; +
| CREATE TABLE table_function_export_def ( +
| id integer NOT NULL, +
| fid integer, +
| CONSTRAINT table_export_base_fkey FOREIGN KEY (fid) REFERENCES table_function_export_def_base(id) +
| ) +
| WITH (orientation=row, compression=no); +
| ALTER TABLE table_function_export_def ADD CONSTRAINT table_function_export_def_pkey PRIMARY KEY USING btree (id);
(1 row)
drop table table_function_export_def;
drop table table_function_export_def_base;
--range table
create table table_range1 (id int, a date, b varchar)
partition by range (id)
(
partition table_range1_p1 values less than(10),
partition table_range1_p2 values less than(50),
partition table_range1_p3 values less than(100),
partition table_range1_p4 values less than(maxvalue)
);
show create table table_range1;
Table | Create Table
--------------+-----------------------------------------------------------
table_range1 | SET search_path = test_get_def; +
| CREATE TABLE table_range1 ( +
| id integer, +
| a date, +
| b character varying +
| ) +
| WITH (orientation=row, compression=no) +
| PARTITION BY RANGE (id) +
| ( +
| PARTITION table_range1_p1 VALUES LESS THAN (10), +
| PARTITION table_range1_p2 VALUES LESS THAN (50), +
| PARTITION table_range1_p3 VALUES LESS THAN (100), +
| PARTITION table_range1_p4 VALUES LESS THAN (MAXVALUE)+
| ) +
| ENABLE ROW MOVEMENT;
(1 row)
drop table table_range1;
--list table
create table table_list1 (id int, a date, b varchar)
partition by list (id)
(
partition table_list1_p1 values (1, 2, 3, 4),
partition table_list1_p2 values (5, 6, 7, 8),
partition table_list1_p3 values (9, 10, 11, 12)
);
show create table table_list1;
Table | Create Table
-------------+--------------------------------------------------
table_list1 | SET search_path = test_get_def; +
| CREATE TABLE table_list1 ( +
| id integer, +
| a date, +
| b character varying +
| ) +
| WITH (orientation=row, compression=no) +
| PARTITION BY LIST (id) +
| ( +
| PARTITION table_list1_p1 VALUES (1,2,3,4), +
| PARTITION table_list1_p2 VALUES (5,6,7,8), +
| PARTITION table_list1_p3 VALUES (9,10,11,12)+
| ) +
| ENABLE ROW MOVEMENT;
(1 row)
drop table table_list1;
--hash table
create table table_hash1 (id int, a date, b varchar)
partition by hash (id)
(
partition table_hash1_p1,
partition table_hash1_p2,
partition table_hash1_p3
);
show create table table_hash1;
Table | Create Table
-------------+----------------------------------------
table_hash1 | SET search_path = test_get_def; +
| CREATE TABLE table_hash1 ( +
| id integer, +
| a date, +
| b character varying +
| ) +
| WITH (orientation=row, compression=no)+
| PARTITION BY HASH (id) +
| ( +
| PARTITION table_hash1_p1, +
| PARTITION table_hash1_p2, +
| PARTITION table_hash1_p3 +
| ) +
| ENABLE ROW MOVEMENT;
(1 row)
drop table table_hash1;
--subpartition table
CREATE TABLE list_range_1 (
col_1 integer primary key,
col_2 integer,
col_3 character varying(30) unique,
col_4 integer
)
WITH (orientation=row, compression=no)
PARTITION BY LIST (col_1) SUBPARTITION BY RANGE (col_2)
(
PARTITION p_list_1 VALUES (-1,-2,-3,-4,-5,-6,-7,-8,-9,-10)
(
SUBPARTITION p_range_1_1 VALUES LESS THAN (-10),
SUBPARTITION p_range_1_2 VALUES LESS THAN (0),
SUBPARTITION p_range_1_3 VALUES LESS THAN (10),
SUBPARTITION p_range_1_4 VALUES LESS THAN (20),
SUBPARTITION p_range_1_5 VALUES LESS THAN (50)
),
PARTITION p_list_2 VALUES (1,2,3,4,5,6,7,8,9,10),
PARTITION p_list_3 VALUES (11,12,13,14,15,16,17,18,19,20)
(
SUBPARTITION p_range_3_1 VALUES LESS THAN (15),
SUBPARTITION p_range_3_2 VALUES LESS THAN (MAXVALUE)
),
PARTITION p_list_4 VALUES (21,22,23,24,25,26,27,28,29,30)
(
SUBPARTITION p_range_4_1 VALUES LESS THAN (-10),
SUBPARTITION p_range_4_2 VALUES LESS THAN (0),
SUBPARTITION p_range_4_3 VALUES LESS THAN (10),
SUBPARTITION p_range_4_4 VALUES LESS THAN (20),
SUBPARTITION p_range_4_5 VALUES LESS THAN (50)
),
PARTITION p_list_5 VALUES (31,32,33,34,35,36,37,38,39,40),
PARTITION p_list_6 VALUES (41,42,43,44,45,46,47,48,49,50)
(
SUBPARTITION p_range_6_1 VALUES LESS THAN (-10),
SUBPARTITION p_range_6_2 VALUES LESS THAN (0),
SUBPARTITION p_range_6_3 VALUES LESS THAN (10),
SUBPARTITION p_range_6_4 VALUES LESS THAN (20),
SUBPARTITION p_range_6_5 VALUES LESS THAN (50)
),
PARTITION p_list_7 VALUES (DEFAULT)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "list_range_1_pkey" for table "list_range_1"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "list_range_1_col_3_tableoid_key" for table "list_range_1"
show create table list_range_1;
Table | Create Table
--------------+------------------------------------------------------------------------------------------------------------------------
list_range_1 | SET search_path = test_get_def; +
| CREATE TABLE list_range_1 ( +
| col_1 integer NOT NULL, +
| col_2 integer, +
| col_3 character varying(30), +
| col_4 integer +
| ) +
| WITH (orientation=row, compression=no) +
| PARTITION BY LIST (col_1) SUBPARTITION BY RANGE (col_2) +
| ( +
| PARTITION p_list_1 VALUES (-1,-2,-3,-4,-5,-6,-7,-8,-9,-10) +
| ( +
| SUBPARTITION p_range_1_1 VALUES LESS THAN (-10), +
| SUBPARTITION p_range_1_2 VALUES LESS THAN (0), +
| SUBPARTITION p_range_1_3 VALUES LESS THAN (10), +
| SUBPARTITION p_range_1_4 VALUES LESS THAN (20), +
| SUBPARTITION p_range_1_5 VALUES LESS THAN (50) +
| ), +
| PARTITION p_list_2 VALUES (1,2,3,4,5,6,7,8,9,10) +
| ( +
| SUBPARTITION p_list_2_subpartdefault1 VALUES LESS THAN (MAXVALUE) +
| ), +
| PARTITION p_list_3 VALUES (11,12,13,14,15,16,17,18,19,20) +
| ( +
| SUBPARTITION p_range_3_1 VALUES LESS THAN (15), +
| SUBPARTITION p_range_3_2 VALUES LESS THAN (MAXVALUE) +
| ), +
| PARTITION p_list_4 VALUES (21,22,23,24,25,26,27,28,29,30) +
| ( +
| SUBPARTITION p_range_4_1 VALUES LESS THAN (-10), +
| SUBPARTITION p_range_4_2 VALUES LESS THAN (0), +
| SUBPARTITION p_range_4_3 VALUES LESS THAN (10), +
| SUBPARTITION p_range_4_4 VALUES LESS THAN (20), +
| SUBPARTITION p_range_4_5 VALUES LESS THAN (50) +
| ), +
| PARTITION p_list_5 VALUES (31,32,33,34,35,36,37,38,39,40) +
| ( +
| SUBPARTITION p_list_5_subpartdefault1 VALUES LESS THAN (MAXVALUE) +
| ), +
| PARTITION p_list_6 VALUES (41,42,43,44,45,46,47,48,49,50) +
| ( +
| SUBPARTITION p_range_6_1 VALUES LESS THAN (-10), +
| SUBPARTITION p_range_6_2 VALUES LESS THAN (0), +
| SUBPARTITION p_range_6_3 VALUES LESS THAN (10), +
| SUBPARTITION p_range_6_4 VALUES LESS THAN (20), +
| SUBPARTITION p_range_6_5 VALUES LESS THAN (50) +
| ), +
| PARTITION p_list_7 VALUES (DEFAULT) +
| ( +
| SUBPARTITION p_list_7_subpartdefault1 VALUES LESS THAN (MAXVALUE) +
| ) +
| ) +
| ENABLE ROW MOVEMENT; +
| ALTER TABLE list_range_1 ADD CONSTRAINT list_range_1_col_3_tableoid_key UNIQUE USING btree (col_3) INCLUDE (tableoid);+
| ALTER TABLE list_range_1 ADD CONSTRAINT list_range_1_pkey PRIMARY KEY USING btree (col_1) INCLUDE (tableoid);
(1 row)
drop table list_range_1;
CREATE TABLE list_hash_2 (
col_1 integer primary key,
col_2 integer,
col_3 character varying(30) unique,
col_4 integer
)
WITH (orientation=row, compression=no)
PARTITION BY LIST (col_2) SUBPARTITION BY HASH (col_3)
(
PARTITION p_list_1 VALUES (-1,-2,-3,-4,-5,-6,-7,-8,-9,-10)
(
SUBPARTITION p_hash_1_1,
SUBPARTITION p_hash_1_2,
SUBPARTITION p_hash_1_3
),
PARTITION p_list_2 VALUES (1,2,3,4,5,6,7,8,9,10),
PARTITION p_list_3 VALUES (11,12,13,14,15,16,17,18,19,20)
(
SUBPARTITION p_hash_3_1,
SUBPARTITION p_hash_3_2
),
PARTITION p_list_4 VALUES (21,22,23,24,25,26,27,28,29,30)
(
SUBPARTITION p_hash_4_1,
SUBPARTITION p_hash_4_2,
SUBPARTITION p_hash_4_3,
SUBPARTITION p_hash_4_4,
SUBPARTITION p_hash_4_5
),
PARTITION p_list_5 VALUES (31,32,33,34,35,36,37,38,39,40),
PARTITION p_list_6 VALUES (41,42,43,44,45,46,47,48,49,50)
(
SUBPARTITION p_hash_6_1,
SUBPARTITION p_hash_6_2,
SUBPARTITION p_hash_6_3,
SUBPARTITION p_hash_6_4,
SUBPARTITION p_hash_6_5
),
PARTITION p_list_7 VALUES (DEFAULT)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "list_hash_2_pkey" for table "list_hash_2"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "list_hash_2_col_3_tableoid_key" for table "list_hash_2"
create unique index list_hash_2_idx1 on list_hash_2(col_2, col_3, col_4) local;
create index list_hash_2_idx2 on list_hash_2(col_3, col_1) local;
create index list_hash_2_idx3 on list_hash_2(col_4) global;
show create table list_hash_2;
Table | Create Table
-------------+----------------------------------------------------------------------------------------------------------------------
list_hash_2 | SET search_path = test_get_def; +
| CREATE TABLE list_hash_2 ( +
| col_1 integer NOT NULL, +
| col_2 integer, +
| col_3 character varying(30), +
| col_4 integer +
| ) +
| WITH (orientation=row, compression=no) +
| PARTITION BY LIST (col_2) SUBPARTITION BY HASH (col_3) +
| ( +
| PARTITION p_list_1 VALUES (-1,-2,-3,-4,-5,-6,-7,-8,-9,-10) +
| ( +
| SUBPARTITION p_hash_1_1, +
| SUBPARTITION p_hash_1_2, +
| SUBPARTITION p_hash_1_3 +
| ), +
| PARTITION p_list_2 VALUES (1,2,3,4,5,6,7,8,9,10) +
| ( +
| SUBPARTITION p_list_2_subpartdefault1 +
| ), +
| PARTITION p_list_3 VALUES (11,12,13,14,15,16,17,18,19,20) +
| ( +
| SUBPARTITION p_hash_3_1, +
| SUBPARTITION p_hash_3_2 +
| ), +
| PARTITION p_list_4 VALUES (21,22,23,24,25,26,27,28,29,30) +
| ( +
| SUBPARTITION p_hash_4_1, +
| SUBPARTITION p_hash_4_2, +
| SUBPARTITION p_hash_4_3, +
| SUBPARTITION p_hash_4_4, +
| SUBPARTITION p_hash_4_5 +
| ), +
| PARTITION p_list_5 VALUES (31,32,33,34,35,36,37,38,39,40) +
| ( +
| SUBPARTITION p_list_5_subpartdefault1 +
| ), +
| PARTITION p_list_6 VALUES (41,42,43,44,45,46,47,48,49,50) +
| ( +
| SUBPARTITION p_hash_6_1, +
| SUBPARTITION p_hash_6_2, +
| SUBPARTITION p_hash_6_3, +
| SUBPARTITION p_hash_6_4, +
| SUBPARTITION p_hash_6_5 +
| ), +
| PARTITION p_list_7 VALUES (DEFAULT) +
| ( +
| SUBPARTITION p_list_7_subpartdefault1 +
| ) +
| ) +
| ENABLE ROW MOVEMENT; +
| CREATE INDEX list_hash_2_idx3 ON list_hash_2 USING btree (col_4) TABLESPACE pg_default; +
| CREATE INDEX list_hash_2_idx2 ON list_hash_2 USING btree (col_3, col_1) LOCAL( +
| PARTITION partition_name( +
| SUBPARTITION p_hash_1_1_col_3_col_1_idx, +
| SUBPARTITION p_hash_1_2_col_3_col_1_idx, +
| SUBPARTITION p_hash_1_3_col_3_col_1_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_list_2_subpartdefault1_col_3_col_1_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_hash_3_1_col_3_col_1_idx, +
| SUBPARTITION p_hash_3_2_col_3_col_1_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_hash_4_1_col_3_col_1_idx, +
| SUBPARTITION p_hash_4_2_col_3_col_1_idx, +
| SUBPARTITION p_hash_4_3_col_3_col_1_idx, +
| SUBPARTITION p_hash_4_4_col_3_col_1_idx, +
| SUBPARTITION p_hash_4_5_col_3_col_1_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_list_5_subpartdefault1_col_3_col_1_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_hash_6_1_col_3_col_1_idx, +
| SUBPARTITION p_hash_6_2_col_3_col_1_idx, +
| SUBPARTITION p_hash_6_3_col_3_col_1_idx, +
| SUBPARTITION p_hash_6_4_col_3_col_1_idx, +
| SUBPARTITION p_hash_6_5_col_3_col_1_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_list_7_subpartdefault1_col_3_col_1_idx +
| ) +
| ) TABLESPACE pg_default; +
| CREATE UNIQUE INDEX list_hash_2_idx1 ON list_hash_2 USING btree (col_2, col_3, col_4) LOCAL( +
| PARTITION partition_name( +
| SUBPARTITION p_hash_1_1_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_1_2_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_1_3_col_2_col_3_col_4_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_list_2_subpartdefault1_col_2_col_3_col_4_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_hash_3_1_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_3_2_col_2_col_3_col_4_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_hash_4_1_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_4_2_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_4_3_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_4_4_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_4_5_col_2_col_3_col_4_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_list_5_subpartdefault1_col_2_col_3_col_4_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_hash_6_1_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_6_2_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_6_3_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_6_4_col_2_col_3_col_4_idx, +
| SUBPARTITION p_hash_6_5_col_2_col_3_col_4_idx +
| ), +
| PARTITION partition_name( +
| SUBPARTITION p_list_7_subpartdefault1_col_2_col_3_col_4_idx +
| ) +
| ) TABLESPACE pg_default; +
| ALTER TABLE list_hash_2 ADD CONSTRAINT list_hash_2_col_3_tableoid_key UNIQUE USING btree (col_3) INCLUDE (tableoid);+
| ALTER TABLE list_hash_2 ADD CONSTRAINT list_hash_2_pkey PRIMARY KEY USING btree (col_1) INCLUDE (tableoid);
(1 row)
drop table list_hash_2;
--interval table
create table table_interval1 (id int, a date, b varchar)
partition by range (a)
interval ('1 day')
(
partition table_interval1_p1 values less than('2020-03-01'),
partition table_interval1_p2 values less than('2020-05-01'),
partition table_interval1_p3 values less than('2020-07-01'),
partition table_interval1_p4 values less than(maxvalue)
);
show create table table_interval1;
Table | Create Table
-----------------+-------------------------------------------------------------------
table_interval1 | SET search_path = test_get_def; +
| CREATE TABLE table_interval1 ( +
| id integer, +
| a date, +
| b character varying +
| ) +
| WITH (orientation=row, compression=no) +
| PARTITION BY RANGE (a) +
| INTERVAL ('1 day') +
| ( +
| PARTITION table_interval1_p1 VALUES LESS THAN ('2020-03-01'),+
| PARTITION table_interval1_p2 VALUES LESS THAN ('2020-05-01'),+
| PARTITION table_interval1_p3 VALUES LESS THAN ('2020-07-01'),+
| PARTITION table_interval1_p4 VALUES LESS THAN (MAXVALUE) +
| ) +
| ENABLE ROW MOVEMENT;
(1 row)
drop table table_interval1;
--generate table
create table generated_test(a int, b int generated always as (length((a)::text)) stored);
show create table generated_test;
Table | Create Table
----------------+--------------------------------------------------------------
generated_test | SET search_path = test_get_def; +
| CREATE TABLE generated_test ( +
| a integer, +
| b integer GENERATED ALWAYS AS (length((a)::text)) STORED+
| ) +
| WITH (orientation=row, compression=no);
(1 row)
-- tests for view
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;
show create view tt19v;
View | Create View | character_set_client | collation_connection
-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
tt19v | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.tt19v AS +| UTF8 | en_US.UTF-8
| 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)
show create view test_get_def.tt19v;
View | Create View | character_set_client | collation_connection
-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
tt19v | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.tt19v AS +| UTF8 | en_US.UTF-8
| 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)
create algorithm = merge view with_c_check_option_view as select * from generated_test where a < 10 with cascaded check option;
create algorithm = merge view with_l_check_option_view as select * from generated_test where a < 10 with local check option;
show create view with_c_check_option_view;
View | Create View | character_set_client | collation_connection
--------------------------+----------------------------------------------------------------------------------------------------------------+----------------------+----------------------
with_c_check_option_view | CREATE OR REPLACE DEFINER = test_showcreate SQL SECURITY DEFINER VIEW test_get_def.with_c_check_option_view AS+| UTF8 | en_US.UTF-8
| SELECT * FROM generated_test WHERE (generated_test.a < 10) WITH CASCADED CHECK OPTION; | |
(1 row)
show create view with_l_check_option_view;
View | Create View | character_set_client | collation_connection
--------------------------+----------------------------------------------------------------------------------------------------------------+----------------------+----------------------
with_l_check_option_view | CREATE OR REPLACE DEFINER = test_showcreate SQL SECURITY DEFINER VIEW test_get_def.with_l_check_option_view AS+| UTF8 | en_US.UTF-8
| SELECT * FROM generated_test WHERE (generated_test.a < 10) WITH LOCAL CHECK OPTION; | |
(1 row)
drop view with_c_check_option_view;
drop view with_l_check_option_view;
-- table view exchange show create test
show create view generated_test;
ERROR: 'test_get_def.generated_test' is not VIEW
show create table tt19v;
View | Create View | character_set_client | collation_connection
-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
tt19v | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.tt19v AS +| UTF8 | en_US.UTF-8
| 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;
drop table generated_test;
-- 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);
show create view tt1;
View | Create View | character_set_client | collation_connection
------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
tt1 | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.tt1 AS +| UTF8 | en_US.UTF-8
| 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); | |
(1 row)
show create table tt1;
View | Create View | character_set_client | collation_connection
------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
tt1 | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.tt1 AS +| UTF8 | en_US.UTF-8
| 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); | |
(1 row)
drop view tt1;
-- 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;
show create view tt20v;
View | Create View | character_set_client | collation_connection
-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
tt20v | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.tt20v AS +| UTF8 | en_US.UTF-8
| 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)
show create table tt20v;
View | Create View | character_set_client | collation_connection
-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
tt20v | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.tt20v AS +| UTF8 | en_US.UTF-8
| 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;
-- Check reverse listing
CREATE TABLE department (
id INTEGER PRIMARY KEY,
parent_department INTEGER ,
name TEXT
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
WITH RECURSIVE subdepartment AS
(
SELECT name as root_name, * FROM department WHERE name = 'A'
UNION ALL
SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
root_name | id | parent_department | name
-----------+----+-------------------+------
(0 rows)
CREATE VIEW vsubdepartment AS
WITH RECURSIVE subdepartment AS
(
SELECT * FROM department WHERE name = 'A'
UNION ALL
SELECT d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment;
show create view vsubdepartment;
View | Create View | character_set_client | collation_connection
----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
vsubdepartment | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.vsubdepartment AS +| UTF8 | en_US.UTF-8
| WITH RECURSIVE subdepartment AS (SELECT * FROM department WHERE (department.name = 'A'::text) UNION ALL SELECT d.* FROM department AS d, subdepartment AS sd WHERE (d.parent_department = sd.id)) SELECT * FROM subdepartment; | |
(1 row)
show create table vsubdepartment;
View | Create View | character_set_client | collation_connection
----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------
vsubdepartment | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.vsubdepartment AS +| UTF8 | en_US.UTF-8
| WITH RECURSIVE subdepartment AS (SELECT * FROM department WHERE (department.name = 'A'::text) UNION ALL SELECT d.* FROM department AS d, subdepartment AS sd WHERE (d.parent_department = sd.id)) SELECT * FROM subdepartment; | |
(1 row)
drop view vsubdepartment;
drop table department;
CREATE TABLE customer (
cid int primary key,
name text not null,
tel text,
passwd text
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
CREATE VIEW my_property_normal AS
SELECT * FROM customer WHERE name = current_user;
CREATE VIEW my_property_secure WITH (security_barrier) AS
SELECT * FROM customer WHERE name = current_user;
show create view my_property_normal;
View | Create View | character_set_client | collation_connection
--------------------+-------------------------------------------------------------------------------------+----------------------+----------------------
my_property_normal | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.my_property_normal AS+| UTF8 | en_US.UTF-8
| SELECT * FROM customer WHERE (customer.name = (`current_user`())::text); | |
(1 row)
show create table my_property_normal;
View | Create View | character_set_client | collation_connection
--------------------+-------------------------------------------------------------------------------------+----------------------+----------------------
my_property_normal | CREATE OR REPLACE DEFINER = test_showcreate VIEW test_get_def.my_property_normal AS+| UTF8 | en_US.UTF-8
| SELECT * FROM customer WHERE (customer.name = (`current_user`())::text); | |
(1 row)
show create view my_property_secure;
View | Create View | character_set_client | collation_connection
--------------------+--------------------------------------------------------------------------------------------------------+----------------------+----------------------
my_property_secure | CREATE OR REPLACE DEFINER = test_showcreate SQL SECURITY DEFINER VIEW test_get_def.my_property_secure +| UTF8 | en_US.UTF-8
| WITH (security_barrier=true) AS +| |
| SELECT * FROM customer WHERE (customer.name = (`current_user`())::text); | |
(1 row)
show create table my_property_secure;
View | Create View | character_set_client | collation_connection
--------------------+--------------------------------------------------------------------------------------------------------+----------------------+----------------------
my_property_secure | CREATE OR REPLACE DEFINER = test_showcreate SQL SECURITY DEFINER VIEW test_get_def.my_property_secure +| UTF8 | en_US.UTF-8
| WITH (security_barrier=true) AS +| |
| SELECT * FROM customer WHERE (customer.name = (`current_user`())::text); | |
(1 row)
drop view my_property_normal;
drop view my_property_secure;
drop table customer;
-- tests for function
-- ARGUMENT and RETURN TYPES
CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01''';
CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql'
AS 'SELECT $1[0]::int';
CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
AS 'SELECT false';
show create function functest_A_1;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+---------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functest_a_1 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functest_a_1(text, date)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 = 'abcd' AND $2 > '2001-01-01'$function$; +| | | |
| | | | |
(1 row)
show create function test_get_def.functest_A_1;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+---------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functest_a_1 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functest_a_1(text, date)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 = 'abcd' AND $2 > '2001-01-01'$function$; +| | | |
| | | | |
(1 row)
show create function test_get_def.test_get_def.functest_A_1;
ERROR: improper func name (too many dotted names): test_get_def.test_get_def.functest_a_1
LINE 1: show create function test_get_def.test_get_def.functest_A_1;
^
show create function functest_A_2;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+-----------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functest_a_2 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functest_a_2(text[])+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS integer +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1[0]::int$function$; +| | | |
| | | | |
(1 row)
show create function functest_A_3;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+-----------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functest_a_3 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functest_a_3()+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT false$function$; +| | | |
| | | | |
(1 row)
drop function functest_A_1;
drop function functest_A_2;
drop function functest_A_3;
-- IMMUTABLE | STABLE | VOLATILE
CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 0';
CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql'
IMMUTABLE AS 'SELECT $1 > 0';
CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql'
STABLE AS 'SELECT $1 = 0';
CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql'
VOLATILE AS 'SELECT $1 < 0';
show create function functest_B_1;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functest_b_1 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functest_b_1(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 > 0$function$; +| | | |
| | | | |
(1 row)
show create function functest_B_2;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functest_b_2 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functest_b_2(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| IMMUTABLE NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 > 0$function$; +| | | |
| | | | |
(1 row)
show create function functest_B_3;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functest_b_3 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functest_b_3(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| STABLE NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 = 0$function$; +| | | |
| | | | |
(1 row)
show create function functest_B_4;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functest_b_4 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functest_b_4(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 < 0$function$; +| | | |
| | | | |
(1 row)
drop function functest_B_1;
drop function functest_B_2;
drop function functest_B_3;
drop function functest_B_4;
-- SECURITY DEFINER | INVOKER
CREATE FUNCTION functext_C_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 0';
CREATE FUNCTION functext_C_2(int) RETURNS bool LANGUAGE 'sql'
SECURITY DEFINER AS 'SELECT $1 = 0';
CREATE FUNCTION functext_C_3(int) RETURNS bool LANGUAGE 'sql'
SECURITY INVOKER AS 'SELECT $1 < 0';
show create function functext_C_1;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_c_1 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_c_1(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 > 0$function$; +| | | |
| | | | |
(1 row)
show create function functext_C_2;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_c_2 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_c_2(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| SECURITY DEFINER NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 = 0$function$; +| | | |
| | | | |
(1 row)
show create function functext_C_3;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_c_3 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_c_3(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 < 0$function$; +| | | |
| | | | |
(1 row)
drop function functext_C_1;
drop function functext_C_2;
drop function functext_C_3;
-- LEAKPROOF
CREATE FUNCTION functext_E_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 100';
CREATE FUNCTION functext_E_2(int) RETURNS bool LANGUAGE 'sql'
LEAKPROOF AS 'SELECT $1 > 100';
show create function functext_E_1;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_e_1 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_e_1(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 > 100$function$; +| | | |
| | | | |
(1 row)
show create function functext_E_2;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_e_2 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_e_2(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| LEAKPROOF NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 > 100$function$; +| | | |
| | | | |
(1 row)
drop function functext_E_1;
drop function functext_E_2;
-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
CREATE FUNCTION functext_F_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 50';
CREATE FUNCTION functext_F_2(int) RETURNS bool LANGUAGE 'sql'
CALLED ON NULL INPUT AS 'SELECT $1 = 50';
CREATE FUNCTION functext_F_3(int) RETURNS bool LANGUAGE 'sql'
RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50';
CREATE FUNCTION functext_F_4(int) RETURNS bool LANGUAGE 'sql'
STRICT AS 'SELECT $1 = 50';
show create function functext_F_1;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_f_1 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_f_1(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 > 50$function$; +| | | |
| | | | |
(1 row)
show create function functext_F_2;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_f_2 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_f_2(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 = 50$function$; +| | | |
| | | | |
(1 row)
show create function functext_F_3;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_f_3 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_f_3(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| STRICT NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 < 50$function$; +| | | |
| | | | |
(1 row)
show create function functext_F_4;
Function | Create Function | sql_mode | character_set_client | collation_connection | Database Collation
--------------+------------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
functext_f_4 | CREATE DEFINER = test_showcreate FUNCTION test_get_def.functext_f_4(integer)+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| RETURNS boolean +| | | |
| LANGUAGE sql +| | | |
| STRICT NOT FENCED NOT SHIPPABLE +| | | |
| AS $function$SELECT $1 = 50$function$; +| | | |
| | | | |
(1 row)
drop function functext_F_1;
drop function functext_F_2;
drop function functext_F_3;
drop function functext_F_4;
-- tests for procedure
CREATE PROCEDURE test_proc_define
(
in_1 IN VARCHAR2,
in_2 VARCHAR2,
out_1 OUT VARCHAR2,
inout_1 IN OUT VARCHAR
)
IS
BEGIN
out_1 := in_1;
inout_1 := inout_1 || in_2;
END;
/
show create procedure test_proc_define;
Procedure | Create Procedure | sql_mode | character_set_client | collation_connection | Database Collation
------------------+---------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
test_proc_define | CREATE DEFINER = test_showcreate PROCEDURE test_get_def.test_proc_define(+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| in_1 IN VARCHAR2, +| | | |
| in_2 VARCHAR2, +| | | |
| out_1 OUT VARCHAR2, +| | | |
| inout_1 IN OUT VARCHAR +| | | |
| ) +| | | |
| AS DECLARE BEGIN +| | | |
| out_1 := in_1; +| | | |
| inout_1 := inout_1 || in_2; +| | | |
| END; +| | | |
| / +| | | |
| | | | |
(1 row)
CREATE PROCEDURE proc_no_parameter
AS
ret1 int;
BEGIN
ret1 := 0;
END;
/
show create procedure proc_no_parameter;
Procedure | Create Procedure | sql_mode | character_set_client | collation_connection | Database Collation
-------------------+-----------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
proc_no_parameter | CREATE DEFINER = test_showcreate PROCEDURE test_get_def.proc_no_parameter()+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| AS DECLARE ret1 int; +| | | |
| BEGIN +| | | |
| ret1 := 0; +| | | |
| END; +| | | |
| / +| | | |
| | | | |
(1 row)
show create procedure test_get_def.proc_no_parameter;
Procedure | Create Procedure | sql_mode | character_set_client | collation_connection | Database Collation
-------------------+-----------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
proc_no_parameter | CREATE DEFINER = test_showcreate PROCEDURE test_get_def.proc_no_parameter()+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| AS DECLARE ret1 int; +| | | |
| BEGIN +| | | |
| ret1 := 0; +| | | |
| END; +| | | |
| / +| | | |
| | | | |
(1 row)
show create procedure test_get_def.test_get_def.proc_no_parameter;
ERROR: improper proc name (too many dotted names): test_get_def.test_get_def.proc_no_parameter
LINE 1: show create procedure test_get_def.test_get_def.proc_no_para...
^
drop procedure proc_no_parameter;
-- Test Replace procedure
--Replace parameter number
CREATE OR REPLACE PROCEDURE test_proc_define
(
in_1 IN VARCHAR2,
in_2 VARCHAR2,
out_1 OUT VARCHAR2
-- inout_1 IN OUT VARCHAR
)
IS
BEGIN
out_1 := in_1;
END;
/
show create procedure test_proc_define;
Procedure | Create Procedure | sql_mode | character_set_client | collation_connection | Database Collation
------------------+---------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
test_proc_define | CREATE DEFINER = test_showcreate PROCEDURE test_get_def.test_proc_define(+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| in_1 IN VARCHAR2, +| | | |
| in_2 VARCHAR2, +| | | |
| out_1 OUT VARCHAR2 +| | | |
| -- inout_1 IN OUT VARCHAR +| | | |
| ) +| | | |
| AS DECLARE BEGIN +| | | |
| out_1 := in_1; +| | | |
| +| | | |
| END; +| | | |
| / +| | | |
| | | | |
(1 row)
--Replace parameter type
CREATE OR REPLACE PROCEDURE test_proc_define
(
in_1 IN INTEGER,
in_2 INTEGER,
out_1 OUT INTEGER
)
IS
BEGIN
out_1 := in_1;
END;
/
show create procedure test_proc_define;
Procedure | Create Procedure | sql_mode | character_set_client | collation_connection | Database Collation
------------------+---------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
test_proc_define | CREATE DEFINER = test_showcreate PROCEDURE test_get_def.test_proc_define(+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| in_1 IN INTEGER, +| | | |
| in_2 INTEGER, +| | | |
| out_1 OUT INTEGER +| | | |
| ) +| | | |
| AS DECLARE BEGIN +| | | |
| out_1 := in_1; +| | | |
| END; +| | | |
| / +| | | |
| | | | |
(1 row)
--Replace parameter NAME
CREATE OR REPLACE PROCEDURE test_proc_define
(
in1 IN INTEGER,
in2 INTEGER,
out1 OUT INTEGER
)
IS
BEGIN
out1 := in1;
END;
/
show create procedure test_proc_define;
Procedure | Create Procedure | sql_mode | character_set_client | collation_connection | Database Collation
------------------+---------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
test_proc_define | CREATE DEFINER = test_showcreate PROCEDURE test_get_def.test_proc_define(+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| in1 IN INTEGER, +| | | |
| in2 INTEGER, +| | | |
| out1 OUT INTEGER +| | | |
| ) +| | | |
| AS DECLARE BEGIN +| | | |
| out1 := in1; +| | | |
| END; +| | | |
| / +| | | |
| | | | |
(1 row)
drop procedure test_proc_define;
-- Test Nested BEGIN/END
CREATE OR REPLACE PROCEDURE test_multi_level
AS
ret INT;
begin
ret := 0;
BEGIN
ret := ret + 1 ;
BEGIN
ret := ret + 1;
END;
END;
END;
/
show create procedure test_multi_level;
Procedure | Create Procedure | sql_mode | character_set_client | collation_connection | Database Collation
------------------+----------------------------------------------------------------------------+-------------------------------------+----------------------+----------------------+--------------------
test_multi_level | CREATE DEFINER = test_showcreate PROCEDURE test_get_def.test_multi_level()+| sql_mode_strict,sql_mode_full_group | UTF8 | en_US.UTF-8 | en_US.UTF-8
| AS DECLARE ret INT; +| | | |
| begin +| | | |
| ret := 0; +| | | |
| BEGIN +| | | |
| ret := ret + 1 ; +| | | |
| BEGIN +| | | |
| ret := ret + 1; +| | | |
| END; +| | | |
| END; +| | | |
| END; +| | | |
| / +| | | |
| | | | |
(1 row)
drop procedure test_multi_level;
-- tests fot trigger
CREATE TABLE main_table (a int, b int);
ALTER TABLE MAIN_TABLE ADD PRIMARY KEY(A, B);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "main_table_pkey" for table "main_table"
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;';
-- tests for per-statement triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
show create trigger before_ins_stmt_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
----------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
before_ins_stmt_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger test_get_def.before_ins_stmt_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
----------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
before_ins_stmt_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger after_ins_stmt_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
---------------------+-------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
after_ins_stmt_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table
EXECUTE PROCEDURE trigger_func('after_upd_stmt');
CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table
FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row');
show create trigger after_upd_stmt_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
---------------------+-------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
after_upd_stmt_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_stmt') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger after_upd_row_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
--------------------+-------------------------------------+---------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
after_upd_row_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
-- test triggers with WHEN clause
CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');
CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table
FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE trigger_func('modified_any');
CREATE TRIGGER insert_a AFTER INSERT ON main_table
FOR EACH ROW WHEN (NEW.a = 123) EXECUTE PROCEDURE trigger_func('insert_a');
CREATE TRIGGER delete_a AFTER DELETE ON main_table
FOR EACH ROW WHEN (OLD.a = 123) EXECUTE PROCEDURE trigger_func('delete_a');
CREATE TRIGGER insert_when BEFORE INSERT ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
CREATE TRIGGER delete_when AFTER DELETE ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
show create trigger modified_a;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
------------+-------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
modified_a | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN ((old.a <> new.a)) EXECUTE PROCEDURE trigger_func('modified_a') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger modified_any;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
--------------+-------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
modified_any | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN ((old.* IS DISTINCT FROM new.*)) EXECUTE PROCEDURE trigger_func('modified_any') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger insert_a;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
----------+-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
insert_a | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER insert_a AFTER INSERT ON main_table FOR EACH ROW WHEN ((new.a = 123)) EXECUTE PROCEDURE trigger_func('insert_a') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger delete_a;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
----------+-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
delete_a | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER delete_a AFTER DELETE ON main_table FOR EACH ROW WHEN ((old.a = 123)) EXECUTE PROCEDURE trigger_func('delete_a') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger insert_when;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
-------------+-------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
insert_when | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER insert_when BEFORE INSERT ON main_table FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger delete_when;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
-------------+-------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
delete_when | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER delete_when AFTER DELETE ON main_table FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
-- Test column-level triggers
CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table
FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row');
CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table
FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row');
CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table
FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row');
CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt');
CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt');
show create trigger before_upd_a_row_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
-----------------------+-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
before_upd_a_row_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('before_upd_a_row') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger after_upd_b_row_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
----------------------+-------------------------------------+------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
after_upd_b_row_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_b_row') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger after_upd_a_b_row_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
------------------------+-------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
after_upd_a_b_row_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_a_b_row') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger before_upd_a_stmt_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
------------------------+-------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
before_upd_a_stmt_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
show create trigger after_upd_b_stmt_trig;
Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation
-----------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------
after_upd_b_stmt_trig | sql_mode_strict,sql_mode_full_group | CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON main_table FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_upd_b_stmt') | UTF8 | en_US.UTF-8 | en_US.UTF-8
(1 row)
drop table main_table;
drop function trigger_func;
-- Check no existence
show create view aa;
ERROR: Table 'test_get_def.aa' doesn't exist
show create table aa;
ERROR: Table 'test_get_def.aa' doesn't exist
show create function aa;
ERROR: FUNCTION aa does not exist
show create procedure aa;
ERROR: PROCEDURE aa does not exist
show create trigger aa;
ERROR: Trigger does not exist
reset current_schema;
drop schema test_get_def cascade;
RESET ROLE;
DROP USER test_showcreate;
drop schema show_create cascade;
reset current_schema;