1-- 2-- IF EXISTS tests 3-- 4 5-- table (will be really dropped at the end) 6 7DROP TABLE test_exists; 8 9DROP TABLE IF EXISTS test_exists; 10 11CREATE TABLE test_exists (a int, b text); 12 13-- view 14 15DROP VIEW test_view_exists; 16 17DROP VIEW IF EXISTS test_view_exists; 18 19CREATE VIEW test_view_exists AS select * from test_exists; 20 21DROP VIEW IF EXISTS test_view_exists; 22 23DROP VIEW test_view_exists; 24 25-- index 26 27DROP INDEX test_index_exists; 28 29DROP INDEX IF EXISTS test_index_exists; 30 31CREATE INDEX test_index_exists on test_exists(a); 32 33DROP INDEX IF EXISTS test_index_exists; 34 35DROP INDEX test_index_exists; 36 37-- sequence 38 39DROP SEQUENCE test_sequence_exists; 40 41DROP SEQUENCE IF EXISTS test_sequence_exists; 42 43CREATE SEQUENCE test_sequence_exists; 44 45DROP SEQUENCE IF EXISTS test_sequence_exists; 46 47DROP SEQUENCE test_sequence_exists; 48 49-- schema 50 51DROP SCHEMA test_schema_exists; 52 53DROP SCHEMA IF EXISTS test_schema_exists; 54 55CREATE SCHEMA test_schema_exists; 56 57DROP SCHEMA IF EXISTS test_schema_exists; 58 59DROP SCHEMA test_schema_exists; 60 61-- type 62 63DROP TYPE test_type_exists; 64 65DROP TYPE IF EXISTS test_type_exists; 66 67CREATE type test_type_exists as (a int, b text); 68 69DROP TYPE IF EXISTS test_type_exists; 70 71DROP TYPE test_type_exists; 72 73-- domain 74 75DROP DOMAIN test_domain_exists; 76 77DROP DOMAIN IF EXISTS test_domain_exists; 78 79CREATE domain test_domain_exists as int not null check (value > 0); 80 81DROP DOMAIN IF EXISTS test_domain_exists; 82 83DROP DOMAIN test_domain_exists; 84 85--- 86--- role/user/group 87--- 88 89CREATE USER regress_test_u1; 90CREATE ROLE regress_test_r1; 91CREATE GROUP regress_test_g1; 92 93DROP USER regress_test_u2; 94 95DROP USER IF EXISTS regress_test_u1, regress_test_u2; 96 97DROP USER regress_test_u1; 98 99DROP ROLE regress_test_r2; 100 101DROP ROLE IF EXISTS regress_test_r1, regress_test_r2; 102 103DROP ROLE regress_test_r1; 104 105DROP GROUP regress_test_g2; 106 107DROP GROUP IF EXISTS regress_test_g1, regress_test_g2; 108 109DROP GROUP regress_test_g1; 110 111-- collation 112DROP COLLATION IF EXISTS test_collation_exists; 113 114-- conversion 115DROP CONVERSION test_conversion_exists; 116DROP CONVERSION IF EXISTS test_conversion_exists; 117CREATE CONVERSION test_conversion_exists 118 FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; 119DROP CONVERSION test_conversion_exists; 120 121-- text search parser 122DROP TEXT SEARCH PARSER test_tsparser_exists; 123DROP TEXT SEARCH PARSER IF EXISTS test_tsparser_exists; 124 125-- text search dictionary 126DROP TEXT SEARCH DICTIONARY test_tsdict_exists; 127DROP TEXT SEARCH DICTIONARY IF EXISTS test_tsdict_exists; 128CREATE TEXT SEARCH DICTIONARY test_tsdict_exists ( 129 Template=ispell, 130 DictFile=ispell_sample, 131 AffFile=ispell_sample 132); 133DROP TEXT SEARCH DICTIONARY test_tsdict_exists; 134 135-- test search template 136DROP TEXT SEARCH TEMPLATE test_tstemplate_exists; 137DROP TEXT SEARCH TEMPLATE IF EXISTS test_tstemplate_exists; 138 139-- text search configuration 140DROP TEXT SEARCH CONFIGURATION test_tsconfig_exists; 141DROP TEXT SEARCH CONFIGURATION IF EXISTS test_tsconfig_exists; 142CREATE TEXT SEARCH CONFIGURATION test_tsconfig_exists (COPY=english); 143DROP TEXT SEARCH CONFIGURATION test_tsconfig_exists; 144 145-- extension 146DROP EXTENSION test_extension_exists; 147DROP EXTENSION IF EXISTS test_extension_exists; 148 149-- functions 150DROP FUNCTION test_function_exists(); 151DROP FUNCTION IF EXISTS test_function_exists(); 152 153DROP FUNCTION test_function_exists(int, text, int[]); 154DROP FUNCTION IF EXISTS test_function_exists(int, text, int[]); 155 156-- aggregate 157DROP AGGREGATE test_aggregate_exists(*); 158DROP AGGREGATE IF EXISTS test_aggregate_exists(*); 159 160DROP AGGREGATE test_aggregate_exists(int); 161DROP AGGREGATE IF EXISTS test_aggregate_exists(int); 162 163-- operator 164DROP OPERATOR @#@ (int, int); 165DROP OPERATOR IF EXISTS @#@ (int, int); 166CREATE OPERATOR @#@ 167 (leftarg = int8, rightarg = int8, procedure = int8xor); 168DROP OPERATOR @#@ (int8, int8); 169 170-- language 171DROP LANGUAGE test_language_exists; 172DROP LANGUAGE IF EXISTS test_language_exists; 173 174-- cast 175DROP CAST (text AS text); 176DROP CAST IF EXISTS (text AS text); 177 178-- trigger 179DROP TRIGGER test_trigger_exists ON test_exists; 180DROP TRIGGER IF EXISTS test_trigger_exists ON test_exists; 181 182DROP TRIGGER test_trigger_exists ON no_such_table; 183DROP TRIGGER IF EXISTS test_trigger_exists ON no_such_table; 184 185DROP TRIGGER test_trigger_exists ON no_such_schema.no_such_table; 186DROP TRIGGER IF EXISTS test_trigger_exists ON no_such_schema.no_such_table; 187 188CREATE TRIGGER test_trigger_exists 189 BEFORE UPDATE ON test_exists 190 FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); 191DROP TRIGGER test_trigger_exists ON test_exists; 192 193-- rule 194DROP RULE test_rule_exists ON test_exists; 195DROP RULE IF EXISTS test_rule_exists ON test_exists; 196 197DROP RULE test_rule_exists ON no_such_table; 198DROP RULE IF EXISTS test_rule_exists ON no_such_table; 199 200DROP RULE test_rule_exists ON no_such_schema.no_such_table; 201DROP RULE IF EXISTS test_rule_exists ON no_such_schema.no_such_table; 202 203CREATE RULE test_rule_exists AS ON INSERT TO test_exists 204 DO INSTEAD 205 INSERT INTO test_exists VALUES (NEW.a, NEW.b || NEW.a::text); 206DROP RULE test_rule_exists ON test_exists; 207 208-- foreign data wrapper 209DROP FOREIGN DATA WRAPPER test_fdw_exists; 210DROP FOREIGN DATA WRAPPER IF EXISTS test_fdw_exists; 211 212-- foreign server 213DROP SERVER test_server_exists; 214DROP SERVER IF EXISTS test_server_exists; 215 216-- operator class 217DROP OPERATOR CLASS test_operator_class USING btree; 218DROP OPERATOR CLASS IF EXISTS test_operator_class USING btree; 219 220DROP OPERATOR CLASS test_operator_class USING no_such_am; 221DROP OPERATOR CLASS IF EXISTS test_operator_class USING no_such_am; 222 223-- operator family 224DROP OPERATOR FAMILY test_operator_family USING btree; 225DROP OPERATOR FAMILY IF EXISTS test_operator_family USING btree; 226 227DROP OPERATOR FAMILY test_operator_family USING no_such_am; 228DROP OPERATOR FAMILY IF EXISTS test_operator_family USING no_such_am; 229 230-- access method 231DROP ACCESS METHOD no_such_am; 232DROP ACCESS METHOD IF EXISTS no_such_am; 233 234-- drop the table 235 236DROP TABLE IF EXISTS test_exists; 237 238DROP TABLE test_exists; 239 240-- be tolerant with missing schemas, types, etc 241 242DROP AGGREGATE IF EXISTS no_such_schema.foo(int); 243DROP AGGREGATE IF EXISTS foo(no_such_type); 244DROP AGGREGATE IF EXISTS foo(no_such_schema.no_such_type); 245DROP CAST IF EXISTS (INTEGER AS no_such_type2); 246DROP CAST IF EXISTS (no_such_type1 AS INTEGER); 247DROP CAST IF EXISTS (INTEGER AS no_such_schema.bar); 248DROP CAST IF EXISTS (no_such_schema.foo AS INTEGER); 249DROP COLLATION IF EXISTS no_such_schema.foo; 250DROP CONVERSION IF EXISTS no_such_schema.foo; 251DROP DOMAIN IF EXISTS no_such_schema.foo; 252DROP FOREIGN TABLE IF EXISTS no_such_schema.foo; 253DROP FUNCTION IF EXISTS no_such_schema.foo(); 254DROP FUNCTION IF EXISTS foo(no_such_type); 255DROP FUNCTION IF EXISTS foo(no_such_schema.no_such_type); 256DROP INDEX IF EXISTS no_such_schema.foo; 257DROP MATERIALIZED VIEW IF EXISTS no_such_schema.foo; 258DROP OPERATOR IF EXISTS no_such_schema.+ (int, int); 259DROP OPERATOR IF EXISTS + (no_such_type, no_such_type); 260DROP OPERATOR IF EXISTS + (no_such_schema.no_such_type, no_such_schema.no_such_type); 261DROP OPERATOR IF EXISTS # (NONE, no_such_schema.no_such_type); 262DROP OPERATOR CLASS IF EXISTS no_such_schema.widget_ops USING btree; 263DROP OPERATOR FAMILY IF EXISTS no_such_schema.float_ops USING btree; 264DROP RULE IF EXISTS foo ON no_such_schema.bar; 265DROP SEQUENCE IF EXISTS no_such_schema.foo; 266DROP TABLE IF EXISTS no_such_schema.foo; 267DROP TEXT SEARCH CONFIGURATION IF EXISTS no_such_schema.foo; 268DROP TEXT SEARCH DICTIONARY IF EXISTS no_such_schema.foo; 269DROP TEXT SEARCH PARSER IF EXISTS no_such_schema.foo; 270DROP TEXT SEARCH TEMPLATE IF EXISTS no_such_schema.foo; 271DROP TRIGGER IF EXISTS foo ON no_such_schema.bar; 272DROP TYPE IF EXISTS no_such_schema.foo; 273DROP VIEW IF EXISTS no_such_schema.foo; 274 275-- Check we receive an ambiguous function error when there are 276-- multiple matching functions. 277CREATE FUNCTION test_ambiguous_funcname(int) returns int as $$ select $1; $$ language sql; 278CREATE FUNCTION test_ambiguous_funcname(text) returns text as $$ select $1; $$ language sql; 279DROP FUNCTION test_ambiguous_funcname; 280DROP FUNCTION IF EXISTS test_ambiguous_funcname; 281 282-- cleanup 283DROP FUNCTION test_ambiguous_funcname(int); 284DROP FUNCTION test_ambiguous_funcname(text); 285 286-- Likewise for procedures. 287CREATE PROCEDURE test_ambiguous_procname(int) as $$ begin end; $$ language plpgsql; 288CREATE PROCEDURE test_ambiguous_procname(text) as $$ begin end; $$ language plpgsql; 289DROP PROCEDURE test_ambiguous_procname; 290DROP PROCEDURE IF EXISTS test_ambiguous_procname; 291 292-- Check we get a similar error if we use ROUTINE instead of PROCEDURE. 293DROP ROUTINE IF EXISTS test_ambiguous_procname; 294 295-- cleanup 296DROP PROCEDURE test_ambiguous_procname(int); 297DROP PROCEDURE test_ambiguous_procname(text); 298 299-- This test checks both the functionality of 'if exists' and the syntax 300-- of the drop database command. 301drop database test_database_exists (force); 302drop database test_database_exists with (force); 303drop database if exists test_database_exists (force); 304drop database if exists test_database_exists with (force); 305