Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-hackers-owner+M314765=pgsql-hackers-arkari@postgresql.org>)
	id 1cS23a-0001KF-43
	for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Jan 2017 13:38:14 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-hackers-owner+M314765=pgsql-hackers-arkari@postgresql.org>)
	id 1cS23Z-0007BR-NS
	for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Jan 2017 13:38:13 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <a.lubennikova@postgrespro.ru>)
	id 1cS228-0003sx-4M
	for pgsql-hackers@postgresql.org; Fri, 13 Jan 2017 13:36:44 +0000
Received: from mail.postgrespro.ru ([93.174.131.138])
	by makus.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <a.lubennikova@postgrespro.ru>)
	id 1cS224-0007LA-BV
	for pgsql-hackers@postgresql.org; Fri, 13 Jan 2017 13:36:42 +0000
Received: from localhost (localhost [127.0.0.1])
	by mail.postgrespro.ru (Postfix) with ESMTP id 0BF6421C26A7
	for <pgsql-hackers@postgresql.org>; Fri, 13 Jan 2017 16:36:38 +0300 (MSK)
X-Virus-Scanned: Debian amavisd-new at postgrespro.ru
To: pgsql-hackers <pgsql-hackers@postgresql.org>
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru;
	s=mail; t=1484314597;
	bh=vVF6y4O8nc5fGswKhAbRXb+mvlSzgaHJD4ueuAZ4H8w=;
	h=From:Subject:Date;
	b=lU6coEi1SPvEm+a1ithRJ01qhVSNHUXuC8/Piwq4+2qppbmYrpRbIsR5mwKsaaSVK
	 MbEBvjUHMF514dJpDJykj6wonWnIVl13OMWObX418f6pSxP6QJT4PSTjTy0aumoxoQ
	 ex6E92u8kwziNA+32KlimSaPSAD0lTIScJF+jkto=
From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Subject: IF NOT EXISTS option for CREATE SERVER and CREATE USER MAPPING
 statements
Message-ID: <7c2ab9b8-388a-1ce0-23a3-7acf2a0ed3c6@postgrespro.ru>
Date: Fri, 13 Jan 2017 16:36:37 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.5.1
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------174053359A3EFD5726E1FB66"
X-Pg-Spam-Score: -5.2 (-----)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------174053359A3EFD5726E1FB66
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

I implemented IF NOT EXISTS option for CREATE SERVER and CREATE USER 
MAPPING statements
for one of our customers.
I think other users can also find it useful for scripting and automated 
tasks.
The patches themselves are small and simple. Documentation is updated as 
well.

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--------------174053359A3EFD5726E1FB66
Content-Type: text/x-patch;
 name="create_foreign_server_if_not_exists.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="create_foreign_server_if_not_exists.patch"

commit cf20fa8c9f29310d8f67e5b198a9eb908d903431
Author: Anastasia <a.lubennikova@postgrespro.ru>
Date:   Fri Jan 13 16:22:01 2017 +0300

    Add [IF NOT EXISTS] option to CREATE SERVER statement

diff --git a/doc/src/sgml/ref/create_server.sgml b/doc/src/sgml/ref/create_server.sgml
index 734c6c9..6777679 100644
--- a/doc/src/sgml/ref/create_server.sgml
+++ b/doc/src/sgml/ref/create_server.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CREATE SERVER <replaceable class="parameter">server_name</replaceable> [ TYPE '<replaceable class="parameter">server_type</replaceable>' ] [ VERSION '<replaceable class="parameter">server_version</replaceable>' ]
+CREATE SERVER [IF NOT EXISTS] <replaceable class="parameter">server_name</replaceable> [ TYPE '<replaceable class="parameter">server_type</replaceable>' ] [ VERSION '<replaceable class="parameter">server_version</replaceable>' ]
     FOREIGN DATA WRAPPER <replaceable class="parameter">fdw_name</replaceable>
     [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
 </synopsis>
@@ -56,6 +56,19 @@ CREATE SERVER <replaceable class="parameter">server_name</replaceable> [ TYPE '<
   <title>Parameters</title>
 
   <variablelist>
+
+  <varlistentry>
+    <term><literal>IF NOT EXISTS</></term>
+    <listitem>
+     <para>
+      Do not throw an error if a server with the same name already exists.
+      A notice is issued in this case.  Note that there is no guarantee that
+      the existing server is anything like the one that would have been
+      created.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><replaceable class="parameter">server_name</replaceable></term>
     <listitem>
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index 06b4bc3..0b0114c 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -883,12 +883,25 @@ CreateForeignServer(CreateForeignServerStmt *stmt)
 
 	/*
 	 * Check that there is no other foreign server by this name.
+	 * Do nothing if IF NOT EXISTS was enforced.
 	 */
 	if (GetForeignServerByName(stmt->servername, true) != NULL)
-		ereport(ERROR,
-				(errcode(ERRCODE_DUPLICATE_OBJECT),
-				 errmsg("server \"%s\" already exists",
-						stmt->servername)));
+	{
+		if (stmt->if_not_exists)
+		{
+			ereport(NOTICE,
+					(errcode(ERRCODE_DUPLICATE_OBJECT),
+					 errmsg("foreign server \"%s\" already exists, skipping",
+							stmt->servername)));
+			heap_close(rel, RowExclusiveLock);
+			return InvalidObjectAddress;
+		}
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_DUPLICATE_OBJECT),
+					 errmsg("foreign server \"%s\" already exists",
+							stmt->servername)));
+	}
 
 	/*
 	 * Check that the FDW exists and that we have USAGE on it. Also get the
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9eef550..ab4b3b1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4621,6 +4621,19 @@ CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
 					n->version = $5;
 					n->fdwname = $9;
 					n->options = $10;
+					n->if_not_exists = false;
+					$$ = (Node *) n;
+				}
+				| CREATE SERVER IF_P NOT EXISTS name opt_type opt_foreign_server_version
+						 FOREIGN DATA_P WRAPPER name create_generic_options
+				{
+					CreateForeignServerStmt *n = makeNode(CreateForeignServerStmt);
+					n->servername = $6;
+					n->servertype = $7;
+					n->version = $8;
+					n->fdwname = $12;
+					n->options = $13;
+					n->if_not_exists = true;
 					$$ = (Node *) n;
 				}
 		;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 7ceaa22..8a79ec0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2075,6 +2075,7 @@ typedef struct CreateForeignServerStmt
 	char	   *servertype;		/* optional server type */
 	char	   *version;		/* optional server version */
 	char	   *fdwname;		/* FDW name */
+	bool		if_not_exists;	/* just do nothing if it already exists? */
 	List	   *options;		/* generic options to server */
 } CreateForeignServerStmt;
 

--------------174053359A3EFD5726E1FB66
Content-Type: text/x-patch;
 name="create_user_mapping_if_not_exists.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="create_user_mapping_if_not_exists.patch"

commit 4237a23d6674da774b0772ffc953e5f499c04803
Author: Anastasia <a.lubennikova@postgrespro.ru>
Date:   Fri Jan 13 16:23:53 2017 +0300

    Add [IF NOT EXISTS] option to CREATE USER MAPPING statement

diff --git a/doc/src/sgml/ref/create_user_mapping.sgml b/doc/src/sgml/ref/create_user_mapping.sgml
index 44fe302..680906b 100644
--- a/doc/src/sgml/ref/create_user_mapping.sgml
+++ b/doc/src/sgml/ref/create_user_mapping.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CREATE USER MAPPING FOR { <replaceable class="parameter">user_name</replaceable> | USER | CURRENT_USER | PUBLIC }
+CREATE USER MAPPING [IF NOT EXISTS] FOR { <replaceable class="parameter">user_name</replaceable> | USER | CURRENT_USER | PUBLIC }
     SERVER <replaceable class="parameter">server_name</replaceable>
     [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [ , ... ] ) ]
 </synopsis>
@@ -49,6 +49,18 @@ CREATE USER MAPPING FOR { <replaceable class="parameter">user_name</replaceable>
  <refsect1>
   <title>Parameters</title>
 
+  <varlistentry>
+    <term><literal>IF NOT EXISTS</></term>
+    <listitem>
+     <para>
+      Do not throw an error if a user mapping with the same name already exists.
+      A notice is issued in this case.  Note that there is no guarantee that
+      the existing user mapping is anything like the one that would have been
+      created.
+     </para>
+    </listitem>
+   </varlistentry>
+
   <variablelist>
    <varlistentry>
     <term><replaceable class="parameter">user_name</replaceable></term>
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index 0b0114c..32fa6a5 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -1172,12 +1172,27 @@ CreateUserMapping(CreateUserMappingStmt *stmt)
 	umId = GetSysCacheOid2(USERMAPPINGUSERSERVER,
 						   ObjectIdGetDatum(useId),
 						   ObjectIdGetDatum(srv->serverid));
+
 	if (OidIsValid(umId))
-		ereport(ERROR,
+	{
+		if (stmt->if_not_exists)
+		{
+			ereport(NOTICE,
+				(errcode(ERRCODE_DUPLICATE_OBJECT),
+				 errmsg("user mapping \"%s\" already exists for server %s, skipping",
+						MappingUserName(useId),
+						stmt->servername)));
+
+			heap_close(rel, RowExclusiveLock);
+			return InvalidObjectAddress;
+		}
+		else
+			ereport(ERROR,
 				(errcode(ERRCODE_DUPLICATE_OBJECT),
 				 errmsg("user mapping \"%s\" already exists for server %s",
 						MappingUserName(useId),
 						stmt->servername)));
+	}
 
 	fdw = GetForeignDataWrapper(srv->fdwid);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ab4b3b1..2fb713f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4897,6 +4897,16 @@ CreateUserMappingStmt: CREATE USER MAPPING FOR auth_ident SERVER name create_gen
 					n->user = $5;
 					n->servername = $7;
 					n->options = $8;
+					n->if_not_exists = false;
+					$$ = (Node *) n;
+				}
+				| CREATE USER MAPPING IF_P NOT EXISTS FOR auth_ident SERVER name create_generic_options
+				{
+					CreateUserMappingStmt *n = makeNode(CreateUserMappingStmt);
+					n->user = $8;
+					n->servername = $10;
+					n->options = $11;
+					n->if_not_exists = true;
 					$$ = (Node *) n;
 				}
 		;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 8a79ec0..bb7c02f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2110,6 +2110,7 @@ typedef struct CreateUserMappingStmt
 	NodeTag		type;
 	RoleSpec   *user;			/* user role */
 	char	   *servername;		/* server name */
+	bool		if_not_exists;	/* just do nothing if it already exists? */
 	List	   *options;		/* generic options to server */
 } CreateUserMappingStmt;
 

--------------174053359A3EFD5726E1FB66
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


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

--------------174053359A3EFD5726E1FB66--