Support CREATE SCHEMA IF NOT EXISTS.

Per discussion, schema-element subcommands are not allowed together with
this option, since it's not very obvious what should happen to the element
objects.

Fabrízio de Royes Mello

2 files changed, 22 insertions, 0 deletions

diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out
index 7c26da5636e..5fcd46daf42 100644
--- a/src/test/regress/expected/namespace.out
+++ b/src/test/regress/expected/namespace.out
@@ -36,6 +36,19 @@ SELECT * FROM test_schema_1.abc_view;
  4 |  
 (3 rows)
 
+-- test IF NOT EXISTS cases
+CREATE SCHEMA test_schema_1; -- fail, already exists
+ERROR:  schema "test_schema_1" already exists
+CREATE SCHEMA IF NOT EXISTS test_schema_1; -- ok with notice
+NOTICE:  schema "test_schema_1" already exists, skipping
+CREATE SCHEMA IF NOT EXISTS test_schema_1 -- fail, disallowed
+       CREATE TABLE abc (
+              a serial,
+              b int UNIQUE
+       );
+ERROR:  CREATE SCHEMA IF NOT EXISTS cannot include schema elements
+LINE 1: CREATE SCHEMA IF NOT EXISTS test_schema_1 
+                                    ^
 DROP SCHEMA test_schema_1 CASCADE;
 NOTICE:  drop cascades to 2 other objects
 DETAIL:  drop cascades to table test_schema_1.abc
diff --git a/src/test/regress/sql/namespace.sql b/src/test/regress/sql/namespace.sql
index 919f72ada2b..879b6c35b05 100644
--- a/src/test/regress/sql/namespace.sql
+++ b/src/test/regress/sql/namespace.sql
@@ -24,6 +24,15 @@ INSERT INTO test_schema_1.abc DEFAULT VALUES;
 SELECT * FROM test_schema_1.abc;
 SELECT * FROM test_schema_1.abc_view;
 
+-- test IF NOT EXISTS cases
+CREATE SCHEMA test_schema_1; -- fail, already exists
+CREATE SCHEMA IF NOT EXISTS test_schema_1; -- ok with notice
+CREATE SCHEMA IF NOT EXISTS test_schema_1 -- fail, disallowed
+       CREATE TABLE abc (
+              a serial,
+              b int UNIQUE
+       );
+
 DROP SCHEMA test_schema_1 CASCADE;
 
 -- verify that the objects were dropped