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