Re: [HACKERS] CREATE COLLATION IF NOT EXISTS

[Peter Eisentraut]

On 2/8/17 10:55 PM, Peter Eisentraut wrote:
> Here is a patch to complete the implementation of CREATE COLLATION IF
> NOT EXISTS.  The meat of this was already implemented for
> pg_import_system_collations; this just exposes it in the SQL command.

committed

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] CREATE COLLATION IF NOT EXISTS

[Peter Eisentraut]

Here is a patch to complete the implementation of CREATE COLLATION IF
NOT EXISTS.  The meat of this was already implemented for
pg_import_system_collations; this just exposes it in the SQL command.

If we go ahead with ICU, then creating collations by hand will become
more common, so this could be useful in practice.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From aa6f7563b0893961bbc5d82c8496c2bbea990f3c Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
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 @@
 
  
 
-CREATE COLLATION name (
+CREATE COLLATION [ IF NOT EXISTS ] name (
 [ LOCALE = locale, ]
 [ LC_COLLATE = lc_collate, ]
 [ LC_CTYPE = lc_ctype ]
 )
-CREATE COLLATION name FROM existing_collation
+CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation
 
  
 
@@ -48,6 +48,17 @@ Parameters
 

 
+ IF NOT EXISTS
+ 
+  
+   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.
+  
+ 
+
+
+
  name
 
  
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;
+	$$ =