1-- This file and its contents are licensed under the Timescale License.
2-- Please see the included NOTICE for copyright information and
3-- LICENSE-TIMESCALE for a copy of the license.
4
5\c :TEST_DBNAME :ROLE_SUPERUSER
6
7CREATE OR REPLACE FUNCTION ts_test_ddl_command_hook_reg() RETURNS VOID
8AS :TSL_MODULE_PATHNAME, 'ts_test_ddl_command_hook_reg'
9LANGUAGE C VOLATILE STRICT;
10
11CREATE OR REPLACE FUNCTION ts_test_ddl_command_hook_unreg() RETURNS VOID
12AS :TSL_MODULE_PATHNAME, 'ts_test_ddl_command_hook_unreg'
13LANGUAGE C VOLATILE STRICT;
14
15SET client_min_messages TO ERROR;
16DROP SCHEMA IF EXISTS htable_schema CASCADE;
17DROP TABLE IF EXISTS htable;
18DROP TABLE IF EXISTS non_htable;
19SET client_min_messages TO NOTICE;
20
21CREATE SCHEMA htable_schema;
22
23-- Install test hooks
24SELECT ts_test_ddl_command_hook_reg();
25
26CREATE TABLE htable(time timestamptz, device int, color int CONSTRAINT color_check CHECK (color > 0), temp float);
27CREATE UNIQUE INDEX htable_pk ON htable(time);
28
29-- CREATE TABLE
30SELECT * FROM create_hypertable('htable', 'time');
31SELECT * FROM test.show_columns('htable');
32SELECT * FROM test.show_constraints('htable');
33SELECT * FROM test.show_indexes('htable');
34
35-- ADD CONSTRAINT
36ALTER TABLE htable ADD CONSTRAINT device_check CHECK (device > 0);
37SELECT * FROM test.show_constraints('htable');
38
39-- DROP CONSTRAINT
40ALTER TABLE htable DROP CONSTRAINT device_check;
41SELECT * FROM test.show_constraints('htable');
42
43-- DROP COLUMN
44ALTER TABLE htable DROP COLUMN color;
45SELECT * FROM test.show_columns('htable');
46
47-- ADD COLUMN
48ALTER TABLE htable ADD COLUMN description text;
49SELECT * FROM test.show_columns('htable');
50
51-- CREATE INDEX
52CREATE INDEX htable_description_idx ON htable (description);
53SELECT * FROM test.show_indexes('htable');
54
55-- CREATE/DROP TRIGGER
56CREATE OR REPLACE FUNCTION test_trigger()
57RETURNS TRIGGER LANGUAGE PLPGSQL AS
58$BODY$
59BEGIN
60RETURN OLD;
61END
62$BODY$;
63
64CREATE TRIGGER htable_trigger_test
65BEFORE INSERT ON htable
66FOR EACH ROW EXECUTE FUNCTION test_trigger();
67
68DROP TRIGGER htable_trigger_test on htable;
69DROP FUNCTION test_trigger();
70
71-- CLUSTER, TRUNCATE, REINDEX, VACUUM (should not call event hooks)
72CREATE TABLE non_htable (id int);
73
74CLUSTER htable USING htable_description_idx;
75TRUNCATE non_htable, htable;
76REINDEX TABLE htable;
77VACUUM htable;
78
79-- ALTER TABLE
80ALTER TABLE htable ADD CONSTRAINT temp_check CHECK (temp > 0.0);
81SELECT * FROM test.show_constraints('htable');
82
83ALTER TABLE htable RENAME CONSTRAINT temp_check TO temp_chk;
84ALTER TABLE htable RENAME COLUMN description TO descr;
85ALTER INDEX htable_description_idx RENAME to htable_descr_idx;
86ALTER TABLE htable SET SCHEMA htable_schema;
87ALTER TABLE htable_schema.htable SET SCHEMA public;
88ALTER TABLE public.htable RENAME TO htable2;
89ALTER TABLE htable2 RENAME TO htable;
90
91-- DROP INDEX, TABLE
92\set ON_ERROR_STOP 0
93DROP INDEX htable_description_idx, htable_pk;
94DROP TABLE htable, non_htable;
95\set ON_ERROR_STOP 1
96DROP INDEX htable_descr_idx;
97DROP TABLE htable;
98DROP TABLE non_htable;
99
100-- DROP TABLE within procedure
101CREATE TABLE test (time timestamp, v int);
102SELECT create_hypertable('test','time');
103CREATE PROCEDURE test_drop() LANGUAGE PLPGSQL AS $$
104BEGIN
105    DROP TABLE test;
106END
107$$;
108CALL test_drop();
109
110-- DROP CASCADE cases
111
112-- DROP schema
113CREATE TABLE htable_schema.non_htable (id int);
114CREATE TABLE htable_schema.htable(time timestamptz, device int, color int, temp float);
115SELECT * FROM create_hypertable('htable_schema.htable', 'time');
116DROP SCHEMA htable_schema CASCADE;
117
118-- DROP column cascades to index drop
119CREATE TABLE htable(time timestamptz, device int, color int, temp float);
120SELECT * FROM create_hypertable('htable', 'time');
121CREATE INDEX htable_device_idx ON htable (device);
122ALTER TABLE htable DROP COLUMN device;
123DROP TABLE htable;
124
125-- DROP foreign key
126CREATE TABLE non_htable (id int PRIMARY KEY);
127CREATE TABLE htable(time timestamptz, device int REFERENCES non_htable(id));
128SELECT * FROM create_hypertable('htable', 'time');
129DROP TABLE non_htable CASCADE;
130
131-- cleanup
132SELECT ts_test_ddl_command_hook_unreg();
133