v20251215-0001-CREATE-VARIABLE-DROP-VARIABLE.patch
text/x-patch
Filename: v20251215-0001-CREATE-VARIABLE-DROP-VARIABLE.patch
Type: text/x-patch
Part: 10
Message:
Re: proposal: schema variables
From 29de9f7c34b2382959dc33634b7e15a12326d559 Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@gmail.com>
Date: Wed, 19 Nov 2025 19:36:07 +0100
Subject: [PATCH 01/11] CREATE VARIABLE, DROP VARIABLE
---
doc/src/sgml/ddl.sgml | 20 ++
doc/src/sgml/glossary.sgml | 15 ++
doc/src/sgml/ref/allfiles.sgml | 2 +
doc/src/sgml/ref/create_variable.sgml | 133 +++++++++++
doc/src/sgml/ref/drop_variable.sgml | 84 +++++++
doc/src/sgml/reference.sgml | 2 +
src/backend/commands/Makefile | 1 +
src/backend/commands/dropcmds.c | 1 +
src/backend/commands/meson.build | 1 +
src/backend/commands/session_variable.c | 215 ++++++++++++++++++
src/backend/parser/gram.y | 55 ++++-
src/backend/tcop/utility.c | 26 +++
src/bin/psql/tab-complete.in.c | 10 +-
src/include/commands/session_variable.h | 25 ++
src/include/nodes/parsenodes.h | 23 ++
src/include/parser/kwlist.h | 1 +
src/include/tcop/cmdtaglist.h | 2 +
.../expected/session_variables_ddl.out | 43 ++++
src/test/regress/parallel_schedule | 2 +-
.../regress/sql/session_variables_ddl.sql | 56 +++++
src/tools/pgindent/typedefs.list | 4 +
21 files changed, 714 insertions(+), 7 deletions(-)
create mode 100644 doc/src/sgml/ref/create_variable.sgml
create mode 100644 doc/src/sgml/ref/drop_variable.sgml
create mode 100644 src/backend/commands/session_variable.c
create mode 100644 src/include/commands/session_variable.h
create mode 100644 src/test/regress/expected/session_variables_ddl.out
create mode 100644 src/test/regress/sql/session_variables_ddl.sql
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index cea28c00f8a..321ea1c4b5b 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5693,6 +5693,26 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
</para>
</sect1>
+ <sect1 id="ddl-session-variables">
+ <title>Session Variables</title>
+
+ <indexterm zone="ddl-session-variables">
+ <primary>Session variables</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ </indexterm>
+
+ <para>
+ Session variables are temporary database objects that can hold a value.
+ A session variable can be created by the <command>CREATE VARIABLE</command>
+ command and can only be accessed by its owner. The value of a session
+ variable is stored in session memory and is private to each session. It is
+ automatically released when the session ends.
+ </para>
+ </sect1>
+
<sect1 id="ddl-others">
<title>Other Database Objects</title>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index a76cf5c383f..64d5fd40219 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1726,6 +1726,21 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-session-variable">
+ <glossterm>Session variable</glossterm>
+ <glossdef>
+ <para>
+ A temporal database object that holds a value in session memory. This
+ value is private to each session and is released when the session ends.
+ The default value of the session variable is null. Read or write access
+ to session variables is allowed only to owner (creator).
+ </para>
+ <para>
+ For more information, see <xref linkend="ddl-session-variables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-shared-memory">
<glossterm>Shared memory</glossterm>
<glossdef>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index e167406c744..a7349919658 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -99,6 +99,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY createType SYSTEM "create_type.sgml">
<!ENTITY createUser SYSTEM "create_user.sgml">
<!ENTITY createUserMapping SYSTEM "create_user_mapping.sgml">
+<!ENTITY createVariable SYSTEM "create_variable.sgml">
<!ENTITY createView SYSTEM "create_view.sgml">
<!ENTITY deallocate SYSTEM "deallocate.sgml">
<!ENTITY declare SYSTEM "declare.sgml">
@@ -147,6 +148,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY dropType SYSTEM "drop_type.sgml">
<!ENTITY dropUser SYSTEM "drop_user.sgml">
<!ENTITY dropUserMapping SYSTEM "drop_user_mapping.sgml">
+<!ENTITY dropVariable SYSTEM "drop_variable.sgml">
<!ENTITY dropView SYSTEM "drop_view.sgml">
<!ENTITY end SYSTEM "end.sgml">
<!ENTITY execute SYSTEM "execute.sgml">
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
new file mode 100644
index 00000000000..4e8c1940252
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,133 @@
+<!--
+doc/src/sgml/ref/create_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-createvariable">
+ <indexterm zone="sql-createvariable">
+ <primary>CREATE VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>defining</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>CREATE VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE VARIABLE</refname>
+ <refpurpose>define a session variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE { TEMP | TEMPORARY } VARIABLE <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command creates a session
+ variable. Currently only temporary session variables are supported,
+ and then the keyword <literal>TEMPORARY</literal> is required.
+ </para>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a session variable's value returns NULL, unless its value is set to
+ something else in the current session with a <command>LET</command> command.
+ The content of a session variable is not transactional. This is the same as
+ regular variables in procedural languages.
+ </para>
+
+ <para>
+ Session variables are retrieved by the <command>SELECT</command>
+ command. Their value is set with the <command>LET</command> command.
+ </para>
+
+ <para>
+ Session variables cannot be used in views or in SQL functions using
+ SQL-conforming style syntax.
+ </para>
+
+ <note>
+ <para>
+ Session variables can be <quote>shadowed</quote> by other identifiers.
+ For details, see <xref linkend="ddl-session-variables"/>.
+ </para>
+ </note>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry id="sql-createvariable-name">
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createvariable-data_type">
+ <term><replaceable class="parameter">data_type</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the data type of the session
+ variable. Only buildin scalar data types are allowed. Arrays or composite
+ types are not allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Use the <command>DROP VARIABLE</command> command to remove a session
+ variable.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Create an date session variable <literal>var1</literal>:
+<programlisting>
+CREATE TEMPORARY VARIABLE var1 AS date;
+</programlisting>
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
new file mode 100644
index 00000000000..e8517a78200
--- /dev/null
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -0,0 +1,84 @@
+<!--
+doc/src/sgml/ref/drop_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-dropvariable">
+ <indexterm zone="sql-dropvariable">
+ <primary>DROP VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>removing</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>DROP VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP VARIABLE</refname>
+ <refpurpose>remove a session variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+DROP VARIABLE <replaceable class="parameter">name</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>DROP VARIABLE</command> removes a session variable.
+ A session variable can only be removed by its owner or a superuser.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To remove the session variable <literal>var1</literal>:
+
+<programlisting>
+DROP VARIABLE var1;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>DROP VARIABLE</command> command is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 2cf02c37b17..c03e7692c7a 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -127,6 +127,7 @@
&createType;
&createUser;
&createUserMapping;
+ &createVariable;
&createView;
&deallocate;
&declare;
@@ -175,6 +176,7 @@
&dropType;
&dropUser;
&dropUserMapping;
+ &dropVariable;
&dropView;
&end;
&execute;
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index 64cb6278409..d42ed8952a2 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -54,6 +54,7 @@ OBJS = \
seclabel.o \
sequence.o \
sequence_xlog.o \
+ session_variable.o \
statscmds.o \
subscriptioncmds.o \
tablecmds.o \
diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c
index ceb9a229b63..9524f867857 100644
--- a/src/backend/commands/dropcmds.c
+++ b/src/backend/commands/dropcmds.c
@@ -22,6 +22,7 @@
#include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
#include "commands/defrem.h"
+#include "commands/session_variable.h"
#include "miscadmin.h"
#include "parser/parse_type.h"
#include "utils/acl.h"
diff --git a/src/backend/commands/meson.build b/src/backend/commands/meson.build
index 5fc35826b1c..1eeb18fd960 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -42,6 +42,7 @@ backend_sources += files(
'seclabel.c',
'sequence.c',
'sequence_xlog.c',
+ 'session_variable.c',
'statscmds.c',
'subscriptioncmds.c',
'tablecmds.c',
diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c
new file mode 100644
index 00000000000..cd61df4a370
--- /dev/null
+++ b/src/backend/commands/session_variable.c
@@ -0,0 +1,215 @@
+/*-------------------------------------------------------------------------
+ *
+ * session_variable.c
+ * session variable creation/manipulation commands
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/commands/session_variable.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "catalog/pg_language.h"
+#include "commands/session_variable.h"
+#include "miscadmin.h"
+#include "parser/parse_type.h"
+#include "storage/proc.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+
+/*
+ * The session variables are stored in the backend's private memory (data,
+ * metadata) in the dedicated memory context SVariableMemoryContext in binary
+ * format. They are stored in the "sessionvars" hash table, whose key is the
+ * name of the variable.
+ *
+ * Only owner (creator) can access the session variables. Because there is
+ * not catalog support, there is not possibility to track dependecies, and
+ * then only buildin types.
+ */
+typedef struct SVariableData
+{
+ NameData varname;
+
+ Oid varowner;
+ Oid vartype;
+ int32 vartypmod;
+ Oid varcollation;
+
+ bool isnull;
+ Datum value;
+
+ int16 typlen;
+ bool typbyval;
+} SVariableData;
+
+typedef SVariableData *SVariable;
+
+static HTAB *sessionvars = NULL; /* hash table for session variables */
+
+static MemoryContext SVariableMemoryContext = NULL;
+
+/*
+ * Create the hash table for storing session variables.
+ */
+static void
+create_sessionvars_hashtables(void)
+{
+ HASHCTL vars_ctl;
+
+ Assert(!sessionvars);
+
+ if (!SVariableMemoryContext)
+ {
+ /* we need our own long-lived memory context */
+ SVariableMemoryContext =
+ AllocSetContextCreate(TopMemoryContext,
+ "session variables",
+ ALLOCSET_START_SMALL_SIZES);
+ }
+
+ vars_ctl.keysize = NAMEDATALEN;
+ vars_ctl.entrysize = sizeof(SVariableData);
+ vars_ctl.hcxt = SVariableMemoryContext;
+
+ sessionvars = hash_create("Session variables", 64, &vars_ctl,
+ HASH_ELEM | HASH_STRINGS | HASH_CONTEXT);
+}
+
+/*
+ * Returns entry of session variable specified by name
+ */
+static SVariable
+search_variable(char *varname)
+{
+ SVariable svar;
+
+ if (!sessionvars)
+ create_sessionvars_hashtables();
+
+ svar = (SVariable) hash_search(sessionvars, varname,
+ HASH_FIND, NULL);
+
+ if (!svar)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("session variable \"%s\" doesn't exist",
+ varname)));
+
+ return svar;
+}
+
+/*
+ * Creates a new variable - does new entry in sessionvars
+ *
+ * Used by CREATE VARIABLE command
+ */
+void
+CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
+{
+ Oid typeid;
+ int32 typmod;
+ Oid typcollation;
+ Oid varowner = GetUserId();
+ SVariable svar;
+ bool found;
+ int16 typlen;
+ bool typbyval;
+
+ /*
+ * Current implementation is not catalog based, but we expect catalog
+ * based implementation for future, so we force same limits.
+ */
+ PreventCommandIfReadOnly("CREATE VARIABLE");
+ PreventCommandIfParallelMode("CREATE VARIABLE");
+ PreventCommandDuringRecovery("CREATE VARIABLE");
+
+ typenameTypeIdAndMod(pstate, stmt->typeName, &typeid, &typmod);
+
+ if (get_typtype(typeid) != TYPTYPE_BASE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s is not a base type",
+ format_type_be(typeid))));
+
+ if (OidIsValid(get_element_type(typeid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("%s type is an array",
+ format_type_be(typeid))));
+
+ /* allow only buildin types */
+ if (typeid >= FirstUnpinnedObjectId)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("session variable cannot have a user-defined type"),
+ errdetail("Session variables that make use of user-defined types are not yet supported."));
+
+ get_typlenbyval(typeid, &typlen, &typbyval);
+ typcollation = get_typcollation(typeid);
+
+ if (!sessionvars)
+ create_sessionvars_hashtables();
+
+ svar = hash_search(sessionvars, stmt->name,
+ HASH_ENTER, &found);
+
+ if (found)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("session variable \"%s\" already exists",
+ stmt->name)));
+
+ namestrcpy(&svar->varname, stmt->name);
+ svar->vartype = typeid;
+ svar->vartypmod = typmod;
+ svar->varcollation = typcollation;
+ svar->varowner = varowner;
+ svar->typlen = typlen;
+ svar->typbyval = typbyval;
+
+ svar->value = (Datum) 0;
+ svar->isnull = true;
+}
+
+/*
+ * Drop variable by name
+ */
+void
+DropVariableByName(char *varname)
+{
+ SVariable svar;
+
+ /*
+ * Current implementation is not catalog based, but we expect catalog
+ * based implementation for future, so we force same limits.
+ */
+ PreventCommandIfReadOnly("DROP VARIABLE");
+ PreventCommandIfParallelMode("DROP VARIABLE");
+ PreventCommandDuringRecovery("DROP VARIABLE");
+
+ svar = search_variable(varname);
+
+ /* 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)));
+
+ if (!svar->typbyval && !svar->isnull)
+ pfree(DatumGetPointer(svar->value));
+
+ if (hash_search(sessionvars,
+ varname,
+ HASH_REMOVE,
+ NULL) == NULL)
+ elog(ERROR, "hash table corrupted");
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 28f4e11e30f..a502554bc75 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -291,13 +291,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt
CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt
- CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt
- CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
+ CreateSchemaStmt CreateSeqStmt CreateSessionVarStmt CreateStmt CreateStatsStmt
+ CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
CreateAssertionStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt
CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
DropOpClassStmt DropOpFamilyStmt DropStmt
- DropCastStmt DropRoleStmt
+ DropCastStmt DropRoleStmt DropSessionVarStmt
DropdbStmt DropTableSpaceStmt
DropTransformStmt
DropUserMappingStmt ExplainStmt FetchStmt
@@ -792,8 +792,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
UNLISTEN UNLOGGED UNTIL UPDATE USER USING
- VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
+ VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARIABLE
+ VARYING VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
WAIT WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -1059,6 +1059,7 @@ stmt:
| CreatePolicyStmt
| CreatePLangStmt
| CreateSchemaStmt
+ | CreateSessionVarStmt
| CreateSeqStmt
| CreateStmt
| CreateSubscriptionStmt
@@ -1086,6 +1087,7 @@ stmt:
| DropTableSpaceStmt
| DropTransformStmt
| DropRoleStmt
+ | DropSessionVarStmt
| DropUserMappingStmt
| DropdbStmt
| ExecuteStmt
@@ -5388,6 +5390,47 @@ create_extension_opt_item:
}
;
+/*****************************************************************************
+ *
+ * QUERY :
+ * CREATE { TEMP | TEMPORARY } VARIABLE varname [AS] type
+ *
+ *****************************************************************************/
+
+CreateSessionVarStmt:
+ CREATE OptTemp VARIABLE 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 = $4;
+ n->typeName = $6;
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
+ * QUERY :
+ * DROP VARIABLE varname
+ *
+ *****************************************************************************/
+
+DropSessionVarStmt:
+ DROP VARIABLE ColId
+ {
+ DropSessionVarStmt *n = makeNode(DropSessionVarStmt);
+
+ n->name = $3;
+ $$ = (Node *) n;
+ }
+ ;
+
/*****************************************************************************
*
* ALTER EXTENSION name UPDATE [ TO version ]
@@ -18208,6 +18251,7 @@ unreserved_keyword:
| VALIDATE
| VALIDATOR
| VALUE_P
+ | VARIABLE
| VARYING
| VERSION_P
| VIEW
@@ -18868,6 +18912,7 @@ bare_label_keyword:
| VALUE_P
| VALUES
| VARCHAR
+ | VARIABLE
| VARIADIC
| VERBOSE
| VERSION_P
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index d18a3a60a46..7dc9c0a6c80 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -48,6 +48,7 @@
#include "commands/schemacmds.h"
#include "commands/seclabel.h"
#include "commands/sequence.h"
+#include "commands/session_variable.h"
#include "commands/subscriptioncmds.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
@@ -183,6 +184,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_CreateRangeStmt:
case T_CreateRoleStmt:
case T_CreateSchemaStmt:
+ case T_CreateSessionVarStmt:
case T_CreateSeqStmt:
case T_CreateStatsStmt:
case T_CreateStmt:
@@ -201,6 +203,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_DropTableSpaceStmt:
case T_DropUserMappingStmt:
case T_DropdbStmt:
+ case T_DropSessionVarStmt:
case T_GrantRoleStmt:
case T_GrantStmt:
case T_ImportForeignSchemaStmt:
@@ -1063,6 +1066,15 @@ standard_ProcessUtility(PlannedStmt *pstmt,
}
break;
+ case T_CreateSessionVarStmt:
+ CreateVariable(pstate, (CreateSessionVarStmt *) parsetree);
+ break;
+
+ case T_DropSessionVarStmt:
+ /* No event triggers for catalog less session variables */
+ DropVariableByName(((DropSessionVarStmt *) parsetree)->name);
+ break;
+
default:
/* All other statement types have event trigger support */
ProcessUtilitySlow(pstate, pstmt, queryString,
@@ -1388,6 +1400,7 @@ ProcessUtilitySlow(ParseState *pstate,
}
break;
+
/*
* ************* object creation / destruction **************
*/
@@ -3235,6 +3248,14 @@ CreateCommandTag(Node *parsetree)
}
break;
+ case T_CreateSessionVarStmt:
+ tag = CMDTAG_CREATE_VARIABLE;
+ break;
+
+ case T_DropSessionVarStmt:
+ tag = CMDTAG_DROP_VARIABLE;
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
@@ -3773,6 +3794,11 @@ GetCommandLogLevel(Node *parsetree)
}
break;
+ case T_CreateSessionVarStmt:
+ case T_DropSessionVarStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index b1ff6f6cd94..8a491c21d7a 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1364,6 +1364,7 @@ static const pgsql_thing_t words_after_create[] = {
{"USER", Query_for_list_of_roles, NULL, NULL, Keywords_for_user_thing},
{"USER MAPPING FOR", NULL, NULL, NULL},
{"VIEW", NULL, NULL, &Query_for_list_of_views},
+ {"VARIABLE", NULL, NULL, NULL, NULL, THING_NO_CREATE},
{NULL} /* end of list */
};
@@ -3737,7 +3738,7 @@ match_previous_words(int pattern_id,
/* CREATE TABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */
else if (TailMatches("CREATE", "TEMP|TEMPORARY"))
- COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW");
+ COMPLETE_WITH("SEQUENCE", "TABLE", "VARIABLE", "VIEW");
/* Complete "CREATE UNLOGGED" with TABLE or SEQUENCE */
else if (TailMatches("CREATE", "UNLOGGED"))
COMPLETE_WITH("TABLE", "SEQUENCE");
@@ -4090,6 +4091,13 @@ match_previous_words(int pattern_id,
COMPLETE_WITH(",", ")");
}
+/* CREATE VARIABLE */
+ else if (Matches("CREATE", "TEMP|TEMPORARY", "VARIABLE", MatchAny))
+ COMPLETE_WITH("AS");
+ else if (TailMatches("VARIABLE", MatchAny, "AS"))
+ /* Complete CREATE VARIABLE <name> with AS types */
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
+
/* CREATE VIEW --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete CREATE [ OR REPLACE ] VIEW <name> with AS or WITH */
else if (TailMatches("CREATE", "VIEW", MatchAny) ||
diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h
new file mode 100644
index 00000000000..1ed40d87a38
--- /dev/null
+++ b/src/include/commands/session_variable.h
@@ -0,0 +1,25 @@
+/*-------------------------------------------------------------------------
+ *
+ * sessionvariable.h
+ * prototypes for sessionvariable.c.
+ *
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/commands/session_variable.h
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#ifndef SESSIONVARIABLE_H
+#define SESSIONVARIABLE_H
+
+#include "catalog/objectaddress.h"
+#include "parser/parse_node.h"
+#include "nodes/parsenodes.h"
+
+extern void CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt);
+extern void DropVariableByName(char *varname);
+
+#endif
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index bc7adba4a0f..517f8a0aad4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3583,6 +3583,29 @@ typedef struct AlterStatsStmt
bool missing_ok; /* skip error if statistics object is missing */
} AlterStatsStmt;
+
+/* ----------------------
+ * Create Variable Statement
+ * ----------------------
+ */
+typedef struct CreateSessionVarStmt
+{
+ NodeTag type;
+ char *name; /* the variable to create */
+ TypeName *typeName; /* the type of variable */
+} CreateSessionVarStmt;
+
+/* ----------------------
+ * DROP Variable Statement
+ * ----------------------
+ */
+typedef struct DropSessionVarStmt
+{
+ NodeTag type;
+ char *name;
+} DropSessionVarStmt;
+
+
/* ----------------------
* Create Function Statement
* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 9fde58f541c..95f2274dc5b 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -491,6 +491,7 @@ PG_KEYWORD("validator", VALIDATOR, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("value", VALUE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("values", VALUES, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("varchar", VARCHAR, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("variable", VARIABLE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index c4606d65043..7e59d0a5275 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -123,6 +123,7 @@ PG_CMDTAG(CMDTAG_CREATE_TRANSFORM, "CREATE TRANSFORM", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_TRIGGER, "CREATE TRIGGER", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_TYPE, "CREATE TYPE", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_USER_MAPPING, "CREATE USER MAPPING", true, false, false)
+PG_CMDTAG(CMDTAG_CREATE_VARIABLE, "CREATE VARIABLE", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_VIEW, "CREATE VIEW", true, false, false)
PG_CMDTAG(CMDTAG_DEALLOCATE, "DEALLOCATE", false, false, false)
PG_CMDTAG(CMDTAG_DEALLOCATE_ALL, "DEALLOCATE ALL", false, false, false)
@@ -175,6 +176,7 @@ PG_CMDTAG(CMDTAG_DROP_TRANSFORM, "DROP TRANSFORM", true, false, false)
PG_CMDTAG(CMDTAG_DROP_TRIGGER, "DROP TRIGGER", true, false, false)
PG_CMDTAG(CMDTAG_DROP_TYPE, "DROP TYPE", true, false, false)
PG_CMDTAG(CMDTAG_DROP_USER_MAPPING, "DROP USER MAPPING", true, false, false)
+PG_CMDTAG(CMDTAG_DROP_VARIABLE, "DROP VARIABLE", true, false, false)
PG_CMDTAG(CMDTAG_DROP_VIEW, "DROP VIEW", true, false, false)
PG_CMDTAG(CMDTAG_EXECUTE, "EXECUTE", false, false, false)
PG_CMDTAG(CMDTAG_EXPLAIN, "EXPLAIN", false, false, false)
diff --git a/src/test/regress/expected/session_variables_ddl.out b/src/test/regress/expected/session_variables_ddl.out
new file mode 100644
index 00000000000..45c2d27ab44
--- /dev/null
+++ b/src/test/regress/expected/session_variables_ddl.out
@@ -0,0 +1,43 @@
+SET log_statement TO ddl;
+-- should to fail
+CREATE VARIABLE x AS int;
+ERROR: only temporal session variables are supported
+-- should be ok
+CREATE TEMPORARY VARIABLE x AS int;
+-- should fail
+CREATE TEMPORARY VARIABLE x AS int;
+ERROR: session variable "x" already exists
+-- should fail
+DROP VARIABLE y;
+ERROR: session variable "y" doesn't exist
+-- should be ok
+DROP VARIABLE x;
+CREATE TYPE test_type AS (x int, y int);
+-- should fail
+CREATE VARIABLE x AS test_type;
+ERROR: only temporal session variables are supported
+DROP TYPE test_type;
+-- should fail
+CREATE VARIABLE x AS int[];
+ERROR: only temporal session variables are supported
+CREATE DOMAIN test_domain AS int;
+-- should fail
+CREATE TEMP VARIABLE x AS test_domain;
+ERROR: test_domain is not a base type
+DROP DOMAIN test_domain;
+CREATE ROLE regress_session_variable_test_role_01;
+CREATE ROLE regress_session_variable_test_role_02;
+SET ROLE TO regress_session_variable_test_role_01;
+CREATE TEMP VARIABLE x AS int;
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_02;
+-- should fail
+DROP VARIABLE x;
+ERROR: must be owner of session variable x
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_01;
+-- should be ok
+DROP VARIABLE x;
+SET ROLE TO DEFAULT;
+DROP ROLE regress_session_variable_test_role_01;
+DROP ROLE regress_session_variable_test_role_02;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 905f9bca959..80668f68944 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -115,7 +115,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson
# NB: temp.sql does reconnects which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml session_variables_ddl
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/session_variables_ddl.sql b/src/test/regress/sql/session_variables_ddl.sql
new file mode 100644
index 00000000000..34f34dd898f
--- /dev/null
+++ b/src/test/regress/sql/session_variables_ddl.sql
@@ -0,0 +1,56 @@
+SET log_statement TO ddl;
+
+-- should to fail
+CREATE VARIABLE x AS int;
+
+-- should be ok
+CREATE TEMPORARY VARIABLE x AS int;
+
+-- should fail
+CREATE TEMPORARY VARIABLE x AS int;
+
+-- should fail
+DROP VARIABLE y;
+
+-- should be ok
+DROP VARIABLE x;
+
+CREATE TYPE test_type AS (x int, y int);
+
+-- should fail
+CREATE VARIABLE x AS test_type;
+
+DROP TYPE test_type;
+
+-- should fail
+CREATE VARIABLE x AS int[];
+
+CREATE DOMAIN test_domain AS int;
+
+-- should fail
+CREATE TEMP VARIABLE x AS test_domain;
+
+DROP DOMAIN test_domain;
+
+CREATE ROLE regress_session_variable_test_role_01;
+CREATE ROLE regress_session_variable_test_role_02;
+
+SET ROLE TO regress_session_variable_test_role_01;
+
+CREATE TEMP VARIABLE x AS int;
+
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_02;
+
+-- should fail
+DROP VARIABLE x;
+
+SET ROLE TO default;
+SET ROLE TO regress_session_variable_test_role_01;
+
+-- should be ok
+DROP VARIABLE x;
+
+SET ROLE TO DEFAULT;
+DROP ROLE regress_session_variable_test_role_01;
+DROP ROLE regress_session_variable_test_role_02;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 3451538565e..7c1a30ba36b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -570,6 +570,7 @@ CreateRoleStmt
CreateSchemaStmt
CreateSchemaStmtContext
CreateSeqStmt
+CreateSessionVarStmt
CreateStatsStmt
CreateStmt
CreateStmtContext
@@ -673,6 +674,7 @@ DropBehavior
DropOwnedStmt
DropReplicationSlotCmd
DropRoleStmt
+DropSessionVarStmt
DropStmt
DropSubscriptionStmt
DropTableSpaceStmt
@@ -2685,6 +2687,8 @@ SSL_CTX
STARTUPINFO
STRLEN
SV
+SVariableData
+SVariable
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
SampleScan
--
2.52.0