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