From fb34e94d214d6767910df47aa7c605c452d11c57 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 3 Oct 2012 19:47:11 -0400 Subject: [PATCH] Support CREATE SCHEMA IF NOT EXISTS. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit 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 --- doc/src/sgml/ref/create_schema.sgml | 27 +++++++++++++++++++ src/backend/commands/extension.c | 1 + src/backend/commands/schemacmds.c | 17 ++++++++++++ src/backend/nodes/copyfuncs.c | 1 + src/backend/nodes/equalfuncs.c | 1 + src/backend/parser/gram.y | 35 +++++++++++++++++++++++++ src/include/nodes/parsenodes.h | 1 + src/test/regress/expected/namespace.out | 13 +++++++++ src/test/regress/sql/namespace.sql | 9 +++++++ 9 files changed, 105 insertions(+) diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index 930d876814..2602bb1d56 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -23,6 +23,8 @@ PostgreSQL documentation CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ] +CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ] +CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name @@ -98,6 +100,17 @@ CREATE SCHEMA AUTHORIZATION user_name + + + IF NOT EXISTS + + + Do nothing (except issuing a notice) if a schema with the same name + already exists. schema_element + subcommands cannot be included when this option is used. + + + @@ -129,6 +142,15 @@ CREATE SCHEMA AUTHORIZATION joe; + + Create a schema named test that will be owned by user + joe, unless there already is a schema named test. + (It does not matter whether joe owns the pre-existing schema.) + +CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe; + + + Create a schema and create a table and view within it: @@ -177,6 +199,11 @@ CREATE VIEW hollywood.winners AS schema owner. This can happen only if the schema owner grants the CREATE privilege on his schema to someone else. + + + The IF NOT EXISTS option is a + PostgreSQL extension. + diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c index 5712fe19c5..5aa9bbb19c 100644 --- a/src/backend/commands/extension.c +++ b/src/backend/commands/extension.c @@ -1376,6 +1376,7 @@ CreateExtension(CreateExtensionStmt *stmt) csstmt->schemaname = schemaName; csstmt->authid = NULL; /* will be created by current user */ csstmt->schemaElts = NIL; + csstmt->if_not_exists = false; CreateSchemaCommand(csstmt, NULL); /* diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c index cd5ce06ca7..e69c86bbab 100644 --- a/src/backend/commands/schemacmds.c +++ b/src/backend/commands/schemacmds.c @@ -83,6 +83,23 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString) errmsg("unacceptable schema name \"%s\"", schemaName), errdetail("The prefix \"pg_\" is reserved for system schemas."))); + /* + * If if_not_exists was given and the schema already exists, bail out. + * (Note: we needn't check this when not if_not_exists, because + * NamespaceCreate will complain anyway.) We could do this before making + * the permissions checks, but since CREATE TABLE IF NOT EXISTS makes its + * creation-permission check first, we do likewise. + */ + if (stmt->if_not_exists && + SearchSysCacheExists1(NAMESPACENAME, PointerGetDatum(schemaName))) + { + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_SCHEMA), + errmsg("schema \"%s\" already exists, skipping", + schemaName))); + return; + } + /* * If the requested authorization is different from the current user, * temporarily set the current user so that the object(s) will be created diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 84ab16d61f..9387ee90c9 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3613,6 +3613,7 @@ _copyCreateSchemaStmt(const CreateSchemaStmt *from) COPY_STRING_FIELD(schemaname); COPY_STRING_FIELD(authid); COPY_NODE_FIELD(schemaElts); + COPY_SCALAR_FIELD(if_not_exists); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index e537e43809..226b99a1d2 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1909,6 +1909,7 @@ _equalCreateSchemaStmt(const CreateSchemaStmt *a, const CreateSchemaStmt *b) COMPARE_STRING_FIELD(schemaname); COMPARE_STRING_FIELD(authid); COMPARE_NODE_FIELD(schemaElts); + COMPARE_SCALAR_FIELD(if_not_exists); return true; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 62ff917828..7feadeac16 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -1169,6 +1169,7 @@ CreateSchemaStmt: n->schemaname = $5; n->authid = $5; n->schemaElts = $6; + n->if_not_exists = false; $$ = (Node *)n; } | CREATE SCHEMA ColId OptSchemaEltList @@ -1178,6 +1179,40 @@ CreateSchemaStmt: n->schemaname = $3; n->authid = NULL; n->schemaElts = $4; + n->if_not_exists = false; + $$ = (Node *)n; + } + | CREATE SCHEMA IF_P NOT EXISTS OptSchemaName AUTHORIZATION RoleId OptSchemaEltList + { + CreateSchemaStmt *n = makeNode(CreateSchemaStmt); + /* One can omit the schema name or the authorization id. */ + if ($6 != NULL) + n->schemaname = $6; + else + n->schemaname = $8; + n->authid = $8; + if ($9 != NIL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CREATE SCHEMA IF NOT EXISTS cannot include schema elements"), + parser_errposition(@9))); + n->schemaElts = $9; + n->if_not_exists = true; + $$ = (Node *)n; + } + | CREATE SCHEMA IF_P NOT EXISTS ColId OptSchemaEltList + { + CreateSchemaStmt *n = makeNode(CreateSchemaStmt); + /* ...but not both */ + n->schemaname = $6; + n->authid = NULL; + if ($7 != NIL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CREATE SCHEMA IF NOT EXISTS cannot include schema elements"), + parser_errposition(@7))); + n->schemaElts = $7; + n->if_not_exists = true; $$ = (Node *)n; } ; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e10e3a1b34..09b15e7694 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1157,6 +1157,7 @@ typedef struct CreateSchemaStmt char *schemaname; /* the name of the schema to create */ char *authid; /* the owner of the created schema */ List *schemaElts; /* schema components (list of parsenodes) */ + bool if_not_exists; /* just do nothing if schema already exists? */ } CreateSchemaStmt; typedef enum DropBehavior diff --git a/src/test/regress/expected/namespace.out b/src/test/regress/expected/namespace.out index 7c26da5636..5fcd46daf4 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 919f72ada2..879b6c35b0 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 -- 2.39.5