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 <pete...@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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers