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