CREATE COLLATION IF NOT EXISTS

From aa6f7563b0893961bbc5d82c8496c2bbea990f3c Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Wed, 8 Feb 2017 22:51:09 -0500
Subject: [PATCH] Add CREATE COLLATION IF NOT EXISTS clause

The core of the functionality was already implemented when
pg_import_system_collations was added.  This just exposes it as an
option in the SQL command.
---
 doc/src/sgml/ref/create_collation.sgml           | 15 +++++++++++++--
 src/backend/commands/collationcmds.c             |  4 ++--
 src/backend/nodes/copyfuncs.c                    |  1 +
 src/backend/nodes/equalfuncs.c                   |  1 +
 src/backend/parser/gram.y                        | 20 ++++++++++++++++++++
 src/backend/tcop/utility.c                       |  3 ++-
 src/include/commands/collationcmds.h             |  2 +-
 src/include/nodes/parsenodes.h                   |  1 +
 src/test/regress/expected/collate.linux.utf8.out |  4 ++++
 src/test/regress/sql/collate.linux.utf8.sql      |  2 ++
 10 files changed, 47 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgml
index d757cdfb43..c09e5bd6d4 100644
--- a/doc/src/sgml/ref/create_collation.sgml
+++ b/doc/src/sgml/ref/create_collation.sgml
@@ -18,12 +18,12 @@
 
  <refsynopsisdiv>
 <synopsis>
-CREATE COLLATION <replaceable>name</replaceable> (
+CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> (
     [ LOCALE = <replaceable>locale</replaceable>, ]
     [ LC_COLLATE = <replaceable>lc_collate</replaceable>, ]
     [ LC_CTYPE = <replaceable>lc_ctype</replaceable> ]
 )
-CREATE COLLATION <replaceable>name</replaceable> FROM <replaceable>existing_collation</replaceable>
+CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replaceable>existing_collation</replaceable>
 </synopsis>
  </refsynopsisdiv>
 
@@ -48,6 +48,17 @@ <title>Parameters</title>
 
    <variablelist>
     <varlistentry>
+     <term><literal>IF NOT EXISTS</literal></term>
+     <listitem>
+      <para>
+       Do not throw an error if a collation with the same name already exists.
+       A notice is issued in this case. Note that there is no guarantee that
+       the existing collation is anything like the one that would have been created.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
      <term><replaceable>name</replaceable></term>
 
      <listitem>
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c
index e165d4b2a6..919cfc6a06 100644
--- a/src/backend/commands/collationcmds.c
+++ b/src/backend/commands/collationcmds.c
@@ -37,7 +37,7 @@
  * CREATE COLLATION
  */
 ObjectAddress
-DefineCollation(ParseState *pstate, List *names, List *parameters)
+DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_exists)
 {
 	char	   *collName;
 	Oid			collNamespace;
@@ -137,7 +137,7 @@ DefineCollation(ParseState *pstate, List *names, List *parameters)
 							 GetDatabaseEncoding(),
 							 collcollate,
 							 collctype,
-							 false);
+							 if_not_exists);
 
 	if (!OidIsValid(newoid))
 		return InvalidObjectAddress;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 30d733e57a..ab96d71a8f 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3104,6 +3104,7 @@ _copyDefineStmt(const DefineStmt *from)
 	COPY_NODE_FIELD(defnames);
 	COPY_NODE_FIELD(args);
 	COPY_NODE_FIELD(definition);
+	COPY_SCALAR_FIELD(if_not_exists);
 
 	return newnode;
 }
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 55c73b7292..94789feb08 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1210,6 +1210,7 @@ _equalDefineStmt(const DefineStmt *a, const DefineStmt *b)
 	COMPARE_NODE_FIELD(defnames);
 	COMPARE_NODE_FIELD(args);
 	COMPARE_NODE_FIELD(definition);
+	COMPARE_SCALAR_FIELD(if_not_exists);
 
 	return true;
 }
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index cf97be512d..7c025f247d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5606,6 +5606,16 @@ DefineStmt:
 					n->definition = $4;
 					$$ = (Node *)n;
 				}
+			| CREATE COLLATION IF_P NOT EXISTS any_name definition
+				{
+					DefineStmt *n = makeNode(DefineStmt);
+					n->kind = OBJECT_COLLATION;
+					n->args = NIL;
+					n->defnames = $6;
+					n->definition = $7;
+					n->if_not_exists = true;
+					$$ = (Node *)n;
+				}
 			| CREATE COLLATION any_name FROM any_name
 				{
 					DefineStmt *n = makeNode(DefineStmt);
@@ -5615,6 +5625,16 @@ DefineStmt:
 					n->definition = list_make1(makeDefElem("from", (Node *) $5, @5));
 					$$ = (Node *)n;
 				}
+			| CREATE COLLATION IF_P NOT EXISTS any_name FROM any_name
+				{
+					DefineStmt *n = makeNode(DefineStmt);
+					n->kind = OBJECT_COLLATION;
+					n->args = NIL;
+					n->defnames = $6;
+					n->definition = list_make1(makeDefElem("from", (Node *) $8, @8));
+					n->if_not_exists = true;
+					$$ = (Node *)n;
+				}
 		;
 
 definition: '(' def_list ')'						{ $$ = $2; }
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 5d3be38bf5..3bc0ae5e7e 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1271,7 +1271,8 @@ ProcessUtilitySlow(ParseState *pstate,
 							Assert(stmt->args == NIL);
 							address = DefineCollation(pstate,
 													  stmt->defnames,
-													  stmt->definition);
+													  stmt->definition,
+													  stmt->if_not_exists);
 							break;
 						default:
 							elog(ERROR, "unrecognized define stmt type: %d",
diff --git a/src/include/commands/collationcmds.h b/src/include/commands/collationcmds.h
index 699ce2f9ee..3b2fcb8271 100644
--- a/src/include/commands/collationcmds.h
+++ b/src/include/commands/collationcmds.h
@@ -18,7 +18,7 @@
 #include "catalog/objectaddress.h"
 #include "nodes/parsenodes.h"
 
-extern ObjectAddress DefineCollation(ParseState *pstate, List *names, List *parameters);
+extern ObjectAddress DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_exists);
 extern void IsThereCollationInNamespace(const char *collname, Oid nspOid);
 
 #endif   /* COLLATIONCMDS_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 07a8436143..5afc3ebea0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2380,6 +2380,7 @@ typedef struct DefineStmt
 	List	   *defnames;		/* qualified name (list of Value strings) */
 	List	   *args;			/* a list of TypeName (if needed) */
 	List	   *definition;		/* a list of DefElem */
+	bool		if_not_exists;	/* just do nothing if it already exists? */
 } DefineStmt;
 
 /* ----------------------
diff --git a/src/test/regress/expected/collate.linux.utf8.out b/src/test/regress/expected/collate.linux.utf8.out
index 286c972fbb..293e78641e 100644
--- a/src/test/regress/expected/collate.linux.utf8.out
+++ b/src/test/regress/expected/collate.linux.utf8.out
@@ -963,6 +963,10 @@ END
 $$;
 CREATE COLLATION test0 FROM "C"; -- fail, duplicate name
 ERROR:  collation "test0" for encoding "UTF8" already exists
+CREATE COLLATION IF NOT EXISTS test0 FROM "C"; -- ok, skipped
+NOTICE:  collation "test0" for encoding "UTF8" already exists, skipping
+CREATE COLLATION IF NOT EXISTS test0 (locale = 'foo'); -- ok, skipped
+NOTICE:  collation "test0" for encoding "UTF8" already exists, skipping
 do $$
 BEGIN
   EXECUTE 'CREATE COLLATION test1 (lc_collate = ' ||
diff --git a/src/test/regress/sql/collate.linux.utf8.sql b/src/test/regress/sql/collate.linux.utf8.sql
index 3b7cc6cf2b..c349cbde2b 100644
--- a/src/test/regress/sql/collate.linux.utf8.sql
+++ b/src/test/regress/sql/collate.linux.utf8.sql
@@ -325,6 +325,8 @@ CREATE SCHEMA test_schema;
 END
 $$;
 CREATE COLLATION test0 FROM "C"; -- fail, duplicate name
+CREATE COLLATION IF NOT EXISTS test0 FROM "C"; -- ok, skipped
+CREATE COLLATION IF NOT EXISTS test0 (locale = 'foo'); -- ok, skipped
 do $$
 BEGIN
   EXECUTE 'CREATE COLLATION test1 (lc_collate = ' ||
-- 
2.11.1