v20251227-0008-support-CREATE-IF-NOT-EXISTS-and-DROP-IF-EXISTS.patch
text/x-patch
Filename: v20251227-0008-support-CREATE-IF-NOT-EXISTS-and-DROP-IF-EXISTS.patch
Type: text/x-patch
Part: 2
Message:
Re: proposal: schema variables
From 9ceadabba58aba2cc7aed481a3bff96f613f3d4d Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Thu, 4 Dec 2025 06:17:54 +0100
Subject: [PATCH 08/11] support CREATE IF NOT EXISTS and DROP IF EXISTS
---
doc/src/sgml/ref/create_variable.sgml | 12 ++++-
doc/src/sgml/ref/drop_variable.sgml | 12 ++++-
src/backend/commands/session_variable.c | 46 +++++++++++++------
src/backend/parser/gram.y | 31 ++++++++++++-
src/backend/tcop/utility.c | 2 +-
src/include/commands/session_variable.h | 2 +-
src/include/nodes/parsenodes.h | 4 +-
.../expected/session_variables_ddl.out | 6 +++
.../regress/sql/session_variables_ddl.sql | 6 +++
9 files changed, 100 insertions(+), 21 deletions(-)
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
index 1315b1248c7..def368fc237 100644
--- a/doc/src/sgml/ref/create_variable.sgml
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -26,7 +26,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
+CREATE { TEMP | TEMPORARY } VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
</synopsis>
</refsynopsisdiv>
<refsect1>
@@ -69,6 +69,16 @@ CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replac
<variablelist>
+ <varlistentry id="sql-createvariable-if-not-exists">
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the name already exists. A notice is issued in
+ this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createvariable-name">
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
index dede42e4ffb..5de6a737493 100644
--- a/doc/src/sgml/ref/drop_variable.sgml
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -26,7 +26,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-DROP VARIABLE <replaceable class="parameter">name</replaceable>
+DROP VARIABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
</synopsis>
</refsynopsisdiv>
@@ -42,6 +42,16 @@ DROP VARIABLE <replaceable class="parameter">name</replaceable>
<refsect1>
<title>Parameters</title>
<variablelist>
+ <varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the session variable does not exist. A notice is
+ issued in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
index 5cc19216f6e..2e3ce5c4017 100644
--- a/src/backend/commands/session_variable.c
+++ b/src/backend/commands/session_variable.c
@@ -92,7 +92,7 @@ create_sessionvars_hashtables(void)
* Returns entry of session variable specified by name
*/
static SVariable
-search_variable(char *varname)
+search_variable(char *varname, bool missing_ok)
{
SVariable svar;
@@ -102,7 +102,7 @@ search_variable(char *varname)
svar = (SVariable) hash_search(sessionvars, varname,
HASH_FIND, NULL);
- if (!svar)
+ if (!svar && !missing_ok)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("session variable \"%s\" doesn't exist",
@@ -124,7 +124,7 @@ get_session_variable_type_typmod_collid(char *varname,
{
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
/* only owner can set content of variable */
*typid = svar->vartype;
@@ -144,7 +144,7 @@ GetSessionVariableWithTypecheck(char *varname,
SVariable svar;
Datum result;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
if (svar->vartype != typid || svar->vartypmod != typmod)
ereport(ERROR,
@@ -181,7 +181,7 @@ SetSessionVariableWithTypecheck(char *varname,
{
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
if (svar->vartype != typid || svar->vartypmod != typmod)
ereport(ERROR,
@@ -277,10 +277,21 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
HASH_ENTER, &found);
if (found)
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("session variable \"%s\" already exists",
- stmt->name)));
+ {
+ if (stmt->if_not_exists)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists, skipping",
+ stmt->name)));
+ return;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists",
+ stmt->name)));
+ }
namestrcpy(&svar->varname, stmt->name);
svar->vartype = typeid;
@@ -298,7 +309,7 @@ CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
* Drop variable by name
*/
void
-DropVariableByName(char *varname)
+DropVariableByName(DropSessionVarStmt *stmt)
{
SVariable svar;
@@ -310,20 +321,27 @@ DropVariableByName(char *varname)
PreventCommandIfParallelMode("DROP VARIABLE");
PreventCommandDuringRecovery("DROP VARIABLE");
- svar = search_variable(varname);
+ svar = search_variable(stmt->name, stmt->missing_ok);
+ if (!svar)
+ {
+ ereport(NOTICE,
+ (errmsg("session variable \"%s\" does not exists, skipping",
+ stmt->name)));
+ return;
+ }
/* only owner can get content of variable */
if (svar->varowner != GetUserId() && !superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be owner of session variable %s",
- varname)));
+ stmt->name)));
if (!svar->typbyval && !svar->isnull)
pfree(DatumGetPointer(svar->value));
if (hash_search(sessionvars,
- varname,
+ stmt->name,
HASH_REMOVE,
NULL) == NULL)
elog(ERROR, "hash table corrupted");
@@ -347,7 +365,7 @@ ExecuteLetStmt(ParseState *pstate,
char *varname = query->resultVariable;
SVariable svar;
- svar = search_variable(varname);
+ svar = search_variable(varname, false);
/* only owner can set content of variable */
if (svar->varowner != GetUserId() && !superuser())
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b26c3116cd9..426a7f1aea4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5394,7 +5394,7 @@ create_extension_opt_item:
/*****************************************************************************
*
* QUERY :
- * CREATE { TEMP | TEMPORARY } VARIABLE varname [AS] type
+ * CREATE { TEMP | TEMPORARY } VARIABLE [IF NOT EXISTS ] varname [AS] type
*
*****************************************************************************/
@@ -5411,14 +5411,31 @@ CreateSessionVarStmt:
n->name = $4;
n->typeName = $6;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE OptTemp VARIABLE IF_P NOT EXISTS ColId opt_as Typename
+ {
+ CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
+
+ if ($2 != RELPERSISTENCE_TEMP)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only temporal session variables are supported"),
+ parser_errposition(@2)));
+
+ n->name = $7;
+ n->typeName = $9;
+ n->if_not_exists = true;
$$ = (Node *) n;
}
+
;
/*****************************************************************************
*
* QUERY :
- * DROP VARIABLE varname
+ * DROP VARIABLE [ IF EXISTS ] varname
*
*****************************************************************************/
@@ -5428,8 +5445,18 @@ DropSessionVarStmt:
DropSessionVarStmt *n = makeNode(DropSessionVarStmt);
n->name = $3;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
+ | DROP VARIABLE IF_P EXISTS ColId
+ {
+ DropSessionVarStmt *n = makeNode(DropSessionVarStmt);
+
+ n->name = $5;
+ n->missing_ok = true;
$$ = (Node *) n;
}
+
;
/*****************************************************************************
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3f1c8cb5e89..fd911f5234f 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1073,7 +1073,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
case T_DropSessionVarStmt:
/* No event triggers for catalog less session variables */
- DropVariableByName(((DropSessionVarStmt *) parsetree)->name);
+ DropVariableByName((DropSessionVarStmt *) parsetree);
break;
case T_LetStmt:
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
index cc1aa7ce23b..3f07ae55aac 100644
--- a/src/include/commands/session_variable.h
+++ b/src/include/commands/session_variable.h
@@ -22,7 +22,7 @@
#include "tcop/cmdtag.h"
extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
-extern void DropVariableByName(char *varname);
+extern void DropVariableByName(DropSessionVarStmt *stmt);
extern Datum GetSessionVariableWithTypecheck(char *varname, Oid typid, int32 typmod, bool *isnull);
extern void SetSessionVariableWithTypecheck(char *varname,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index fbe118bde04..517bfd91f22 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3620,6 +3620,7 @@ typedef struct CreateSessionVarStmt
NodeTag type;
char *name; /* the variable to create */
TypeName *typeName; /* the type of variable */
+ bool if_not_exists; /* just do nothing if variable already exists? */
} CreateSessionVarStmt;
/* ----------------------
@@ -3629,7 +3630,8 @@ typedef struct CreateSessionVarStmt
typedef struct DropSessionVarStmt
{
NodeTag type;
- char *name;
+ char *name; /* the variable name to drop */
+ bool missing_ok; /* skip error of variable is missing */
} DropSessionVarStmt;
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
index c36febd894e..9f5b088de72 100644
--- a/src/test/regress/expected/session_variables_ddl.out
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -48,3 +48,9 @@ ERROR: session variable "x" already exists
DISCARD TEMP;
-- should be ok
CREATE TEMP VARIABLE x AS int;
+-- should be ok
+CREATE TEMP VARIABLE IF NOT EXISTS x AS int;
+NOTICE: session variable "x" already exists, skipping
+DROP VARIABLE x;
+DROP VARIABLE IF EXISTS x;
+NOTICE: session variable "x" does not exists, skipping
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
index 7fd739d6677..60f78671e3b 100644
--- a/src/test/regress/sql/session_variables_ddl.sql
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -64,3 +64,9 @@ DISCARD TEMP;
-- should be ok
CREATE TEMP VARIABLE x AS int;
+
+-- should be ok
+CREATE TEMP VARIABLE IF NOT EXISTS x AS int;
+
+DROP VARIABLE x;
+DROP VARIABLE IF EXISTS x;
--
2.52.0