Commits

Anonymous committed fb34e94

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

Comments (0)

Files changed (9)

doc/src/sgml/ref/create_schema.sgml

 <synopsis>
 CREATE SCHEMA <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">user_name</replaceable> ] [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
 CREATE SCHEMA AUTHORIZATION <replaceable class="parameter">user_name</replaceable> [ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
+CREATE SCHEMA IF NOT EXISTS <replaceable class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable class="parameter">user_name</replaceable> ]
+CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">user_name</replaceable>
 </synopsis>
  </refsynopsisdiv>
 
        </para>
       </listitem>
      </varlistentry>
+
+     <varlistentry>
+      <term><literal>IF NOT EXISTS</literal></term>
+      <listitem>
+       <para>
+        Do nothing (except issuing a notice) if a schema with the same name
+        already exists.  <replaceable class="parameter">schema_element</>
+        subcommands cannot be included when this option is used.
+       </para>
+      </listitem>
+     </varlistentry>
     </variablelist>
  </refsect1>
 
   </para>
 
   <para>
+   Create a schema named <literal>test</> that will be owned by user
+   <literal>joe</>, unless there already is a schema named <literal>test</>.
+   (It does not matter whether <literal>joe</> owns the pre-existing schema.)
+<programlisting>
+CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;
+</programlisting>
+  </para>
+
+  <para>
    Create a schema and create a table and view within it:
 <programlisting>
 CREATE SCHEMA hollywood
    schema owner.  This can happen only if the schema owner grants the
    <literal>CREATE</> privilege on his schema to someone else.
   </para>
+
+  <para>
+   The <literal>IF NOT EXISTS</literal> option is a
+   <productname>PostgreSQL</productname> extension.
+  </para>
  </refsect1>
 
  <refsect1>

src/backend/commands/extension.c

 			csstmt->schemaname = schemaName;
 			csstmt->authid = NULL;		/* will be created by current user */
 			csstmt->schemaElts = NIL;
+			csstmt->if_not_exists = false;
 			CreateSchemaCommand(csstmt, NULL);
 
 			/*

src/backend/commands/schemacmds.c

 		   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
 	 * with the correct ownership.

src/backend/nodes/copyfuncs.c

 	COPY_STRING_FIELD(schemaname);
 	COPY_STRING_FIELD(authid);
 	COPY_NODE_FIELD(schemaElts);
+	COPY_SCALAR_FIELD(if_not_exists);
 
 	return newnode;
 }

src/backend/nodes/equalfuncs.c

 	COMPARE_STRING_FIELD(schemaname);
 	COMPARE_STRING_FIELD(authid);
 	COMPARE_NODE_FIELD(schemaElts);
+	COMPARE_SCALAR_FIELD(if_not_exists);
 
 	return true;
 }

src/backend/parser/gram.y

 						n->schemaname = $5;
 					n->authid = $5;
 					n->schemaElts = $6;
+					n->if_not_exists = false;
 					$$ = (Node *)n;
 				}
 			| CREATE SCHEMA ColId OptSchemaEltList
 					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;
 				}
 		;

src/include/nodes/parsenodes.h

 	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

src/test/regress/expected/namespace.out

  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

src/test/regress/sql/namespace.sql

 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
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.