pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed[PATCH 11/11] documentation
12+ messages / 8 participants
[nested] [flat]
Commits from this thread:
2026-01-07 ece25c2611 d3a2781e58 8dd074abd0 77ade60a0a 818ab0116a c813007376 createuser: Update docs to reflect defaults
2025-12-19 44f49511b7 cf8c8adfe3 Update pg_hba.conf example to reflect MD5 deprecation
* [PATCH 11/11] documentation
@ 2022-04-04 18:23 okbob@github.com <okbob@github.com>
0 siblings, 0 replies; 12+ messages in thread
From: okbob@github.com @ 2022-04-04 18:23 UTC (permalink / raw)
Documentation for CREATE VARIABLE, DROP VARIABLE and LET commands. Update of
GRANT, REVOKE, DISCARD, ALTER commands related to support of session variables.
---
doc/src/sgml/advanced.sgml | 62 +++++
doc/src/sgml/catalogs.sgml | 158 +++++++++++++
doc/src/sgml/config.sgml | 15 ++
doc/src/sgml/event-trigger.sgml | 24 ++
doc/src/sgml/glossary.sgml | 16 ++
doc/src/sgml/plpgsql.sgml | 12 +
doc/src/sgml/ref/allfiles.sgml | 4 +
.../sgml/ref/alter_default_privileges.sgml | 26 ++-
doc/src/sgml/ref/alter_variable.sgml | 179 +++++++++++++++
doc/src/sgml/ref/comment.sgml | 1 +
doc/src/sgml/ref/create_variable.sgml | 214 ++++++++++++++++++
doc/src/sgml/ref/discard.sgml | 14 +-
doc/src/sgml/ref/drop_variable.sgml | 118 ++++++++++
doc/src/sgml/ref/grant.sgml | 9 +
doc/src/sgml/ref/let.sgml | 109 +++++++++
doc/src/sgml/ref/pg_restore.sgml | 11 +
doc/src/sgml/ref/revoke.sgml | 7 +
doc/src/sgml/reference.sgml | 4 +
18 files changed, 974 insertions(+), 9 deletions(-)
create mode 100644 doc/src/sgml/ref/alter_variable.sgml
create mode 100644 doc/src/sgml/ref/create_variable.sgml
create mode 100644 doc/src/sgml/ref/drop_variable.sgml
create mode 100644 doc/src/sgml/ref/let.sgml
inline patch 1: doc/src/sgml/advanced.sgml, doc/src/sgml/catalogs.sgml, doc/src/sgml/config.sgml and 15 more (+974/-9 lines) download inline-1.patch
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 755c9f1485..c65b2bb336 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -700,6 +700,68 @@ SELECT name, elevation
</sect1>
+ <sect1 id="tutorial-session-variables">
+ <title>Session Variables</title>
+
+ <indexterm zone="tutorial-session-variables">
+ <primary>Session variables</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>introduction</secondary>
+ </indexterm>
+
+ <para>
+ Session variables are database objects that can hold a value.
+ Session variables, like relations, exist within a schema and their access
+ is controlled via <command>GRANT</command> and <command>REVOKE</command>
+ commands. A session variable can be created by the <command>CREATE
+ VARIABLE</command> command.
+ </para>
+
+ <para>
+ The value of a session variable is set with the <command>LET</command> SQL
+ command. While session variables share properties with tables, their value
+ cannot be updated with an <command>UPDATE</command> command. The value of a
+ session variable may be retrieved by the <command>SELECT</command> SQL
+ command.
+<programlisting>
+CREATE VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT var1;
+</programlisting>
+
+ or
+
+<programlisting>
+CREATE VARIABLE public.current_user_id AS integer;
+GRANT READ ON VARIABLE public.current_user_id TO PUBLIC;
+LET current_user_id = (SELECT id FROM users WHERE usename = session_user);
+SELECT current_user_id;
+</programlisting>
+ </para>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a variable's value returns either a <literal>NULL</literal> or a default
+ value, unless its value has been set to something else in the current
+ session using the <command>LET</command> command. The content of a variable
+ is not transactional. This is the same as regular variables in PL
+ languages.
+ </para>
+
+ <para>
+ The session variables can be shadowed by column references in a query. When
+ a query contains identifiers or qualified identifiers that could be used as
+ both a session variable identifiers and as column identifier, then the
+ column identifier is preferred every time. Warnings can be emitted when
+ this situation happens by enabling configuration parameter <xref
+ linkend="guc-session-variables-ambiguity-warning"/>.
+ </para>
+ </sect1>
+
+
<sect1 id="tutorial-conclusion">
<title>Conclusion</title>
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 00f833d210..e5294be4a2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -369,6 +369,11 @@
<entry><link linkend="catalog-pg-user-mapping"><structname>pg_user_mapping</structname></link></entry>
<entry>mappings of users to foreign servers</entry>
</row>
+
+ <row>
+ <entry><link linkend="catalog-pg-variable"><structname>pg_variable</structname></link></entry>
+ <entry>session variables</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -9613,4 +9618,157 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</table>
</sect1>
+ <sect1 id="catalog-pg-variable">
+ <title><structname>pg_variable</structname></title>
+
+ <indexterm zone="catalog-pg-variable">
+ <primary>pg_variable</primary>
+ </indexterm>
+
+ <para>
+ The table <structname>pg_variable</structname> provides information about
+ session variables.
+ </para>
+
+ <table>
+ <title><structname>pg_variable</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>oid</structfield> <type>oid</type>
+ </para>
+ <para>
+ Row identifier
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the session variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varnamespace</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The OID of the namespace that contains this variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vartype</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-type"><structname>pg_type</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The OID of the variable's data type
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vartypmod</structfield> <type>int4</type>
+ </para>
+ <para>
+ <structfield>vartypmod</structfield> records type-specific data
+ supplied at variable creation time (for example, the maximum
+ length of a <type>varchar</type> column). It is passed to
+ type-specific input functions and length coercion functions.
+ The value will generally be -1 for types that do not need <structfield>vartypmod</structfield>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varowner</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Owner of the variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varcollation</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The defined collation of the variable, or zero if the variable is
+ not of a collatable data type.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varisnotnull</structfield> <type>boolean</type>
+ </para>
+ <para>
+ True if the session variable doesn't allow null value. The default value is false.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varisimmutable</structfield> <type>boolean</type>
+ </para>
+ <para>
+ True if the variable is <link linkend="sql-createvariable-immutable">immutable</link> (cannot be modified). The default value is false.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vareoxaction</structfield> <type>char</type>
+ </para>
+ <para>
+ Action performed at end of transaction:
+ <literal>n</literal> = no action, <literal>d</literal> = drop the variable,
+ <literal>r</literal> = reset the variable to its default value.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vardefexpr</structfield> <type>pg_node_tree</type>
+ </para>
+ <para>
+ The internal representation of the variable default value
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varacl</structfield> <type>aclitem[]</type>
+ </para>
+ <para>
+ Access privileges; see
+ <xref linkend="sql-grant"/> and
+ <xref linkend="sql-revoke"/>
+ for details
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
</chapter>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a5cd4e44c7..6bf0f59998 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10239,6 +10239,21 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
</listitem>
</varlistentry>
+ <varlistentry id="guc-session-variables-ambiguity-warning" xreflabel="session_variables_ambiguity_warning">
+ <term><varname>session_variables_ambiguity_warning</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>session_variables_ambiguity_warning</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ When on, a warning is raised when any identifier in a query could be
+ used as both a column identifier, routine variable or a session
+ variable identifier. The default is <literal>off</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
<term><varname>standard_conforming_strings</varname> (<type>boolean</type>)
<indexterm><primary>strings</primary><secondary>standard conforming</secondary></indexterm>
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index f1235a2c9f..a8f303b474 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -405,6 +405,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>ALTER VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>ALTER VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
@@ -693,6 +701,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>CREATE VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>CREATE VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
@@ -981,6 +997,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>DROP VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>DROP VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index d6d0a3a814..a46baa0c17 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1477,6 +1477,22 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-session-variable">
+ <glossterm>Session variable</glossterm>
+ <glossdef>
+ <para>
+ A persistent database object that holds a value in session memory. This
+ memory is not shared across sessions, and after session end, this memory
+ (the value) is released. The access (read or write) to session variables
+ is controlled by access rights similarly to other database object access
+ rights.
+ </para>
+ <para>
+ For more information, see <xref linkend="tutorial-session-variables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-shared-memory">
<glossterm>Shared memory</glossterm>
<glossdef>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index cf387dfc3f..3dfe4c8a2e 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -5953,6 +5953,18 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE;
</programlisting>
</para>
</sect3>
+
+ <sect3>
+ <title><command>Session variables</command></title>
+
+ <para>
+ The <application>PL/pgSQL</application> language has no packages, and
+ therefore no package variables or package constants.
+ <productname>PostgreSQL</productname> has session variables and immutable
+ session variables. Session variables can be created by <command>CREATE
+ VARIABLE</command>, as described in <xref linkend="sql-createvariable"/>.
+ </para>
+ </sect3>
</sect2>
<sect2 id="plpgsql-porting-appendix">
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index e90a0e1f83..24e30fdd97 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -47,6 +47,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY alterType SYSTEM "alter_type.sgml">
<!ENTITY alterUser SYSTEM "alter_user.sgml">
<!ENTITY alterUserMapping SYSTEM "alter_user_mapping.sgml">
+<!ENTITY alterVariable SYSTEM "alter_variable.sgml">
<!ENTITY alterView SYSTEM "alter_view.sgml">
<!ENTITY analyze SYSTEM "analyze.sgml">
<!ENTITY begin SYSTEM "begin.sgml">
@@ -99,6 +100,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">
@@ -148,6 +150,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY dropUser SYSTEM "drop_user.sgml">
<!ENTITY dropUserMapping SYSTEM "drop_user_mapping.sgml">
<!ENTITY dropView SYSTEM "drop_view.sgml">
+<!ENTITY dropVariable SYSTEM "drop_variable.sgml">
<!ENTITY end SYSTEM "end.sgml">
<!ENTITY execute SYSTEM "execute.sgml">
<!ENTITY explain SYSTEM "explain.sgml">
@@ -155,6 +158,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY grant SYSTEM "grant.sgml">
<!ENTITY importForeignSchema SYSTEM "import_foreign_schema.sgml">
<!ENTITY insert SYSTEM "insert.sgml">
+<!ENTITY let SYSTEM "let.sgml">
<!ENTITY listen SYSTEM "listen.sgml">
<!ENTITY load SYSTEM "load.sgml">
<!ENTITY lock SYSTEM "lock.sgml">
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index f1d54f5aa3..354991b849 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -50,6 +50,10 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+GRANT { SELECT | UPDATE | ALL [ PRIVILEGES ] }
+ ON VARIABLES
+ TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
@@ -81,6 +85,12 @@ REVOKE [ GRANT OPTION FOR ]
ON SCHEMAS
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON VARIABLES
+ FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
@@ -92,14 +102,14 @@ REVOKE [ GRANT OPTION FOR ]
that will be applied to objects created in the future. (It does not
affect privileges assigned to already-existing objects.) Currently,
only the privileges for schemas, tables (including views and foreign
- tables), sequences, functions, and types (including domains) can be
- altered. For this command, functions include aggregates and procedures.
- The words <literal>FUNCTIONS</literal> and <literal>ROUTINES</literal> are
- equivalent in this command. (<literal>ROUTINES</literal> is preferred
- going forward as the standard term for functions and procedures taken
- together. In earlier PostgreSQL releases, only the
- word <literal>FUNCTIONS</literal> was allowed. It is not possible to set
- default privileges for functions and procedures separately.)
+ tables), sequences, functions, types (including domains) and session
+ variables can be altered. For this command, functions include aggregates
+ and procedures. The words <literal>FUNCTIONS</literal> and
+ <literal>ROUTINES</literal> are equivalent in this command.
+ (<literal>ROUTINES</literal> is preferred going forward as the standard term
+ for functions and procedures taken together. In earlier PostgreSQL
+ releases, only the word <literal>FUNCTIONS</literal> was allowed. It is not
+ possible to set default privileges for functions and procedures separately.)
</para>
<para>
diff --git a/doc/src/sgml/ref/alter_variable.sgml b/doc/src/sgml/ref/alter_variable.sgml
new file mode 100644
index 0000000000..d2036351e5
--- /dev/null
+++ b/doc/src/sgml/ref/alter_variable.sgml
@@ -0,0 +1,179 @@
+<!--
+doc/src/sgml/ref/alter_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-altervariable">
+ <indexterm zone="sql-altervariable">
+ <primary>ALTER VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>altering</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>ALTER VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>ALTER VARIABLE</refname>
+ <refpurpose>
+ change the definition of a session variable
+ </refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>ALTER VARIABLE</command> command changes the definition of an
+ existing session variable. There are several subforms:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>OWNER</literal></term>
+ <listitem>
+ <para>
+ This form changes the owner of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RENAME</literal></term>
+ <listitem>
+ <para>
+ This form changes the name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SET SCHEMA</literal></term>
+ <listitem>
+ <para>
+ This form moves the session variable into another schema.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
+ <para>
+ Only the owner or a superuser is allowed to alter a session variable.
+ In order to move a session variable from one schema to another, the user
+ must also have the <literal>CREATE</literal> privilege on the new schema (or
+ be a superuser).
+
+ In order to move the session variable ownership from one role to another,
+ the user must also be a direct or indirect member of the new
+ owning role, and that role must have the <literal>CREATE</literal> privilege
+ on the session variable's schema (or be a superuser). These restrictions
+ enforce that altering the owner doesn't do anything you couldn't do by
+ dropping and recreating the session variable.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <para>
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (possibly schema-qualified) of the existing session variable
+ to alter.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_name</replaceable></term>
+ <listitem>
+ <para>
+ The new name for the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_owner</replaceable></term>
+ <listitem>
+ <para>
+ The user name of the new owner of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_schema</replaceable></term>
+ <listitem>
+ <para>
+ The new schema for the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To rename a session variable:
+<programlisting>
+ALTER VARIABLE foo RENAME TO boo;
+</programlisting>
+ </para>
+
+ <para>
+ To change the owner of the session variable <literal>boo</literal> to
+ <literal>joe</literal>:
+<programlisting>
+ALTER VARIABLE boo OWNER TO joe;
+</programlisting>
+ </para>
+
+ <para>
+ To change the schema of the session variable <literal>boo</literal> to
+ <literal>private</literal>:
+<programlisting>
+ALTER VARIABLE boo SET SCHEMA private;
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ Session variables and this command in particular are a PostgreSQL extension.
+ </para>
+ </refsect1>
+
+ <refsect1 id="sql-altervariable-see-also">
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 23d9029af9..50bfec6f28 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -65,6 +65,7 @@ COMMENT ON
TRANSFORM FOR <replaceable>type_name</replaceable> LANGUAGE <replaceable>lang_name</replaceable> |
TRIGGER <replaceable class="parameter">trigger_name</replaceable> ON <replaceable class="parameter">table_name</replaceable> |
TYPE <replaceable class="parameter">object_name</replaceable> |
+ VARIABLE <replaceable class="parameter">object_name</replaceable> |
VIEW <replaceable class="parameter">object_name</replaceable>
} IS '<replaceable class="parameter">text</replaceable>'
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
new file mode 100644
index 0000000000..70c87968ce
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,214 @@
+<!--
+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 [ { TEMPORARY | TEMP } ] [ IMMUTABLE ] VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceable class="parameter">collation</replaceable> ]
+ [ NOT NULL ] [ DEFAULT <replaceable class="parameter">default_expr</replaceable> ] [ { ON COMMIT DROP | ON TRANSACTION END RESET } ]
+</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command creates a session variable.
+ Session variables, like relations, exist within a schema and their access is
+ controlled via <command>GRANT</command> and <command>REVOKE</command>
+ commands.
+ </para>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a session variable's value returns either a NULL or a default value, unless
+ its value is set to something else in the current session with a LET
+ command. The content of a session variable is not transactional. This is the
+ same as regular variables in PL languages.
+ </para>
+
+ <para>
+ Session variables are retrieved by the <command>SELECT</command> SQL
+ command. Their value is set with the <command>LET</command> SQL command.
+ While session variables share properties with tables, their value cannot be
+ changed with an <command>UPDATE</command> command.
+ </para>
+
+ <note>
+ <para>
+ Inside a query or an expression, the session variable can be shadowed by
+ column or by routine's variable or routine argument. Such collisions of
+ identifiers can be resolved by using qualified identifiers. Session variables
+ can use schema name, columns can use table aliases, routine variables
+ can use block labels, and routine arguments can use the routine name.
+ </para>
+ </note>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry id="sql-createvariable-immutable">
+ <term><literal>IMMUTABLE</literal></term>
+ <listitem>
+ <para>
+ The assigned value of the session variable can not be changed.
+ Only if the session variable doesn't have a default value, a single
+ initialization is allowed using the <command>LET</command> command. Once
+ done, no further change is allowed until end of transaction
+ if the session variable was created with clause <literal>ON TRANSACTION
+ END RESET</literal>, or until reset of all session variables by
+ <command>DISCARD VARIABLES</command>, or until reset of all session
+ objects by command <command>DISCARD ALL</command>.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <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>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">data_type</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the data type of the session
+ variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>COLLATE <replaceable>collation</replaceable></literal></term>
+ <listitem>
+ <para>
+ The <literal>COLLATE</literal> clause assigns a collation to the session
+ variable (which must be of a collatable data type). If not specified,
+ the data type's default collation is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>NOT NULL</literal></term>
+ <listitem>
+ <para>
+ The <literal>NOT NULL</literal> clause forbids setting the session
+ variable to a null value. A session variable created as NOT NULL and
+ without an explicitly declared default value cannot be read until it is
+ initialized by a LET command. This requires the user to explicitly
+ initialize the session variable content before reading it, otherwise an
+ error will be thrown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DEFAULT <replaceable>default_expr</replaceable></literal></term>
+ <listitem>
+ <para>
+ The <literal>DEFAULT</literal> clause can be used to assign a default
+ value to a session variable. This expression is evaluated when the session
+ variable is first accessed for reading and had not yet been assigned a value.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ON COMMIT DROP</literal>, <literal>ON TRANSACTION END RESET</literal></term>
+ <listitem>
+ <para>
+ The <literal>ON COMMIT DROP</literal> clause specifies the behaviour of a
+ temporary session variable at transaction commit. With this clause, the
+ session variable is dropped at commit time. The clause is only allowed
+ for temporary variables. The <literal>ON TRANSACTION END RESET</literal>
+ clause causes the session variable to be reset to its default value when
+ the transaction is committed or rolled back.
+ </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 VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT var1;
+</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-altervariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index bf44c523ca..6f90672afa 100644
--- a/doc/src/sgml/ref/discard.sgml
+++ b/doc/src/sgml/ref/discard.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
+DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP | VARIABLES }
</synopsis>
</refsynopsisdiv>
@@ -75,6 +75,17 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>VARIABLES</literal></term>
+ <listitem>
+ <para>
+ Resets the value of all session variables. If a variable
+ is later reused, it is re-initialized to either
+ <literal>NULL</literal> or its default value.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>ALL</literal></term>
<listitem>
@@ -93,6 +104,7 @@ SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;
DISCARD SEQUENCES;
+DISCARD VARIABLES;
</programlisting></para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
new file mode 100644
index 0000000000..67988b5fcd
--- /dev/null
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -0,0 +1,118 @@
+<!--
+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 [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
+</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><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>
+ <para>
+ The name, optionally schema-qualified, of a session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>CASCADE</literal></term>
+ <listitem>
+ <para>
+ Automatically drop objects that depend on the session variable (such as
+ views), and in turn all objects that depend on those objects
+ (see <xref linkend="ddl-depend"/>).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESTRICT</literal></term>
+ <listitem>
+ <para>
+ Refuse to drop the session variable if any objects depend on it. This is
+ the default.
+ </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-altervariable"/></member>
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index dea19cd348..e2191b7767 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -108,6 +108,15 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
+
+GRANT { SELECT | UPDATE | ALL [ PRIVILEGES ] }
+ ON VARIABLE <replaceable>variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
+
+ TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
+
</synopsis>
</refsynopsisdiv>
diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml
new file mode 100644
index 0000000000..c5d1e4d962
--- /dev/null
+++ b/doc/src/sgml/ref/let.sgml
@@ -0,0 +1,109 @@
+<!--
+doc/src/sgml/ref/let.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-let">
+ <indexterm zone="sql-let">
+ <primary>LET</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>changing</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>LET</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>LET</refname>
+ <refpurpose>change a session variable's value</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+LET <replaceable class="parameter">session_variable</replaceable> = <replaceable class="parameter">sql_expression</replaceable>
+LET <replaceable class="parameter">session_variable</replaceable> = DEFAULT
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>LET</command> command assigns a value to the specified session
+ variable.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>session_variable</literal></term>
+ <listitem>
+ <para>
+ The name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sql_expression</literal></term>
+ <listitem>
+ <para>
+ An SQL expression (can be subquery in parenthesis). The result must
+ be of castable to the same data type as the session variable (in
+ implicit or assignment context).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DEFAULT</literal></term>
+ <listitem>
+ <para>
+ Reset the session variable to its default value, if that is defined.
+ If no explicit default value has been declared, the session variable
+ is set to NULL.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ Example:
+<programlisting>
+CREATE VARIABLE myvar AS integer;
+LET myvar = 10;
+LET myvar = (SELECT sum(val) FROM tab);
+LET myvar = DEFAULT;
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>LET</command> is a <productname>PostgreSQL</productname>
+ extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-altervariable"/></member>
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 47bd7dbda0..39cb595647 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -106,6 +106,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-A <replaceable class="parameter">session_variable</replaceable></option></term>
+ <term><option>--variable=<replaceable class="parameter">session_variable</replaceable></option></term>
+ <listitem>
+ <para>
+ Restore a named session variable only. Multiple session variables may
+ be specified with multiple <option>-A</option> switches.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-c</option></term>
<term><option>--clean</option></term>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 4fd4bfb3d7..0579b4599d 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -137,6 +137,13 @@ REVOKE [ { ADMIN | INHERIT } OPTION FOR ]
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON VARIABLE <replaceable>variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index a3b743e8c1..f5ec3c987b 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -75,6 +75,7 @@
&alterType;
&alterUser;
&alterUserMapping;
+ &alterVariable;
&alterView;
&analyze;
&begin;
@@ -127,6 +128,7 @@
&createType;
&createUser;
&createUserMapping;
+ &createVariable;
&createView;
&deallocate;
&declare;
@@ -175,6 +177,7 @@
&dropType;
&dropUser;
&dropUserMapping;
+ &dropVariable;
&dropView;
&end;
&execute;
@@ -183,6 +186,7 @@
&grant;
&importForeignSchema;
&insert;
+ &let;
&listen;
&load;
&lock;
--
2.37.2
* [PATCH v20220922 13/13] documentation
@ 2022-04-04 18:23 okbob@github.com <okbob@github.com>
0 siblings, 0 replies; 12+ messages in thread
From: okbob@github.com @ 2022-04-04 18:23 UTC (permalink / raw)
Documentation for CREATE VARIABLE, DROP VARIABLE and LET commands. Update of
GRANT, REVOKE, DISCARD, ALTER commands related to support of session variables.
---
doc/src/sgml/advanced.sgml | 62 +++++
doc/src/sgml/catalogs.sgml | 158 +++++++++++++
doc/src/sgml/config.sgml | 15 ++
doc/src/sgml/event-trigger.sgml | 24 ++
doc/src/sgml/glossary.sgml | 16 ++
doc/src/sgml/plpgsql.sgml | 12 +
doc/src/sgml/ref/allfiles.sgml | 4 +
.../sgml/ref/alter_default_privileges.sgml | 26 ++-
doc/src/sgml/ref/alter_variable.sgml | 179 +++++++++++++++
doc/src/sgml/ref/comment.sgml | 1 +
doc/src/sgml/ref/create_variable.sgml | 214 ++++++++++++++++++
doc/src/sgml/ref/discard.sgml | 14 +-
doc/src/sgml/ref/drop_variable.sgml | 118 ++++++++++
doc/src/sgml/ref/grant.sgml | 9 +
doc/src/sgml/ref/let.sgml | 109 +++++++++
doc/src/sgml/ref/pg_restore.sgml | 11 +
doc/src/sgml/ref/revoke.sgml | 7 +
doc/src/sgml/reference.sgml | 4 +
18 files changed, 974 insertions(+), 9 deletions(-)
create mode 100644 doc/src/sgml/ref/alter_variable.sgml
create mode 100644 doc/src/sgml/ref/create_variable.sgml
create mode 100644 doc/src/sgml/ref/drop_variable.sgml
create mode 100644 doc/src/sgml/ref/let.sgml
inline patch 1: doc/src/sgml/advanced.sgml, doc/src/sgml/catalogs.sgml, doc/src/sgml/config.sgml and 15 more (+974/-9 lines) download inline-1.patch
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 755c9f1485..c65b2bb336 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -700,6 +700,68 @@ SELECT name, elevation
</sect1>
+ <sect1 id="tutorial-session-variables">
+ <title>Session Variables</title>
+
+ <indexterm zone="tutorial-session-variables">
+ <primary>Session variables</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>introduction</secondary>
+ </indexterm>
+
+ <para>
+ Session variables are database objects that can hold a value.
+ Session variables, like relations, exist within a schema and their access
+ is controlled via <command>GRANT</command> and <command>REVOKE</command>
+ commands. A session variable can be created by the <command>CREATE
+ VARIABLE</command> command.
+ </para>
+
+ <para>
+ The value of a session variable is set with the <command>LET</command> SQL
+ command. While session variables share properties with tables, their value
+ cannot be updated with an <command>UPDATE</command> command. The value of a
+ session variable may be retrieved by the <command>SELECT</command> SQL
+ command.
+<programlisting>
+CREATE VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT var1;
+</programlisting>
+
+ or
+
+<programlisting>
+CREATE VARIABLE public.current_user_id AS integer;
+GRANT READ ON VARIABLE public.current_user_id TO PUBLIC;
+LET current_user_id = (SELECT id FROM users WHERE usename = session_user);
+SELECT current_user_id;
+</programlisting>
+ </para>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a variable's value returns either a <literal>NULL</literal> or a default
+ value, unless its value has been set to something else in the current
+ session using the <command>LET</command> command. The content of a variable
+ is not transactional. This is the same as regular variables in PL
+ languages.
+ </para>
+
+ <para>
+ The session variables can be shadowed by column references in a query. When
+ a query contains identifiers or qualified identifiers that could be used as
+ both a session variable identifiers and as column identifier, then the
+ column identifier is preferred every time. Warnings can be emitted when
+ this situation happens by enabling configuration parameter <xref
+ linkend="guc-session-variables-ambiguity-warning"/>.
+ </para>
+ </sect1>
+
+
<sect1 id="tutorial-conclusion">
<title>Conclusion</title>
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 00f833d210..e5294be4a2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -369,6 +369,11 @@
<entry><link linkend="catalog-pg-user-mapping"><structname>pg_user_mapping</structname></link></entry>
<entry>mappings of users to foreign servers</entry>
</row>
+
+ <row>
+ <entry><link linkend="catalog-pg-variable"><structname>pg_variable</structname></link></entry>
+ <entry>session variables</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -9613,4 +9618,157 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</table>
</sect1>
+ <sect1 id="catalog-pg-variable">
+ <title><structname>pg_variable</structname></title>
+
+ <indexterm zone="catalog-pg-variable">
+ <primary>pg_variable</primary>
+ </indexterm>
+
+ <para>
+ The table <structname>pg_variable</structname> provides information about
+ session variables.
+ </para>
+
+ <table>
+ <title><structname>pg_variable</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>oid</structfield> <type>oid</type>
+ </para>
+ <para>
+ Row identifier
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the session variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varnamespace</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The OID of the namespace that contains this variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vartype</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-type"><structname>pg_type</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The OID of the variable's data type
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vartypmod</structfield> <type>int4</type>
+ </para>
+ <para>
+ <structfield>vartypmod</structfield> records type-specific data
+ supplied at variable creation time (for example, the maximum
+ length of a <type>varchar</type> column). It is passed to
+ type-specific input functions and length coercion functions.
+ The value will generally be -1 for types that do not need <structfield>vartypmod</structfield>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varowner</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Owner of the variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varcollation</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The defined collation of the variable, or zero if the variable is
+ not of a collatable data type.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varisnotnull</structfield> <type>boolean</type>
+ </para>
+ <para>
+ True if the session variable doesn't allow null value. The default value is false.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varisimmutable</structfield> <type>boolean</type>
+ </para>
+ <para>
+ True if the variable is <link linkend="sql-createvariable-immutable">immutable</link> (cannot be modified). The default value is false.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vareoxaction</structfield> <type>char</type>
+ </para>
+ <para>
+ Action performed at end of transaction:
+ <literal>n</literal> = no action, <literal>d</literal> = drop the variable,
+ <literal>r</literal> = reset the variable to its default value.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vardefexpr</structfield> <type>pg_node_tree</type>
+ </para>
+ <para>
+ The internal representation of the variable default value
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varacl</structfield> <type>aclitem[]</type>
+ </para>
+ <para>
+ Access privileges; see
+ <xref linkend="sql-grant"/> and
+ <xref linkend="sql-revoke"/>
+ for details
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
</chapter>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 700914684d..939a124e8c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10239,6 +10239,21 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
</listitem>
</varlistentry>
+ <varlistentry id="guc-session-variables-ambiguity-warning" xreflabel="session_variables_ambiguity_warning">
+ <term><varname>session_variables_ambiguity_warning</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>session_variables_ambiguity_warning</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ When on, a warning is raised when any identifier in a query could be
+ used as both a column identifier, routine variable or a session
+ variable identifier. The default is <literal>off</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings">
<term><varname>standard_conforming_strings</varname> (<type>boolean</type>)
<indexterm><primary>strings</primary><secondary>standard conforming</secondary></indexterm>
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index f1235a2c9f..a8f303b474 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -405,6 +405,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>ALTER VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>ALTER VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
@@ -693,6 +701,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>CREATE VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>CREATE VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
@@ -981,6 +997,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>DROP VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>DROP VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index d6d0a3a814..a46baa0c17 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1477,6 +1477,22 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-session-variable">
+ <glossterm>Session variable</glossterm>
+ <glossdef>
+ <para>
+ A persistent database object that holds a value in session memory. This
+ memory is not shared across sessions, and after session end, this memory
+ (the value) is released. The access (read or write) to session variables
+ is controlled by access rights similarly to other database object access
+ rights.
+ </para>
+ <para>
+ For more information, see <xref linkend="tutorial-session-variables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-shared-memory">
<glossterm>Shared memory</glossterm>
<glossdef>
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index d85f89bf30..cf73e44732 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -5953,6 +5953,18 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE;
</programlisting>
</para>
</sect3>
+
+ <sect3>
+ <title><command>Session variables</command></title>
+
+ <para>
+ The <application>PL/pgSQL</application> language has no packages, and
+ therefore no package variables or package constants.
+ <productname>PostgreSQL</productname> has session variables and immutable
+ session variables. Session variables can be created by <command>CREATE
+ VARIABLE</command>, as described in <xref linkend="sql-createvariable"/>.
+ </para>
+ </sect3>
</sect2>
<sect2 id="plpgsql-porting-appendix">
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index e90a0e1f83..24e30fdd97 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -47,6 +47,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY alterType SYSTEM "alter_type.sgml">
<!ENTITY alterUser SYSTEM "alter_user.sgml">
<!ENTITY alterUserMapping SYSTEM "alter_user_mapping.sgml">
+<!ENTITY alterVariable SYSTEM "alter_variable.sgml">
<!ENTITY alterView SYSTEM "alter_view.sgml">
<!ENTITY analyze SYSTEM "analyze.sgml">
<!ENTITY begin SYSTEM "begin.sgml">
@@ -99,6 +100,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">
@@ -148,6 +150,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY dropUser SYSTEM "drop_user.sgml">
<!ENTITY dropUserMapping SYSTEM "drop_user_mapping.sgml">
<!ENTITY dropView SYSTEM "drop_view.sgml">
+<!ENTITY dropVariable SYSTEM "drop_variable.sgml">
<!ENTITY end SYSTEM "end.sgml">
<!ENTITY execute SYSTEM "execute.sgml">
<!ENTITY explain SYSTEM "explain.sgml">
@@ -155,6 +158,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY grant SYSTEM "grant.sgml">
<!ENTITY importForeignSchema SYSTEM "import_foreign_schema.sgml">
<!ENTITY insert SYSTEM "insert.sgml">
+<!ENTITY let SYSTEM "let.sgml">
<!ENTITY listen SYSTEM "listen.sgml">
<!ENTITY load SYSTEM "load.sgml">
<!ENTITY lock SYSTEM "lock.sgml">
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index f1d54f5aa3..354991b849 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -50,6 +50,10 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+GRANT { SELECT | UPDATE | ALL [ PRIVILEGES ] }
+ ON VARIABLES
+ TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
@@ -81,6 +85,12 @@ REVOKE [ GRANT OPTION FOR ]
ON SCHEMAS
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON VARIABLES
+ FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
@@ -92,14 +102,14 @@ REVOKE [ GRANT OPTION FOR ]
that will be applied to objects created in the future. (It does not
affect privileges assigned to already-existing objects.) Currently,
only the privileges for schemas, tables (including views and foreign
- tables), sequences, functions, and types (including domains) can be
- altered. For this command, functions include aggregates and procedures.
- The words <literal>FUNCTIONS</literal> and <literal>ROUTINES</literal> are
- equivalent in this command. (<literal>ROUTINES</literal> is preferred
- going forward as the standard term for functions and procedures taken
- together. In earlier PostgreSQL releases, only the
- word <literal>FUNCTIONS</literal> was allowed. It is not possible to set
- default privileges for functions and procedures separately.)
+ tables), sequences, functions, types (including domains) and session
+ variables can be altered. For this command, functions include aggregates
+ and procedures. The words <literal>FUNCTIONS</literal> and
+ <literal>ROUTINES</literal> are equivalent in this command.
+ (<literal>ROUTINES</literal> is preferred going forward as the standard term
+ for functions and procedures taken together. In earlier PostgreSQL
+ releases, only the word <literal>FUNCTIONS</literal> was allowed. It is not
+ possible to set default privileges for functions and procedures separately.)
</para>
<para>
diff --git a/doc/src/sgml/ref/alter_variable.sgml b/doc/src/sgml/ref/alter_variable.sgml
new file mode 100644
index 0000000000..d2036351e5
--- /dev/null
+++ b/doc/src/sgml/ref/alter_variable.sgml
@@ -0,0 +1,179 @@
+<!--
+doc/src/sgml/ref/alter_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-altervariable">
+ <indexterm zone="sql-altervariable">
+ <primary>ALTER VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>altering</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>ALTER VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>ALTER VARIABLE</refname>
+ <refpurpose>
+ change the definition of a session variable
+ </refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>ALTER VARIABLE</command> command changes the definition of an
+ existing session variable. There are several subforms:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>OWNER</literal></term>
+ <listitem>
+ <para>
+ This form changes the owner of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RENAME</literal></term>
+ <listitem>
+ <para>
+ This form changes the name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SET SCHEMA</literal></term>
+ <listitem>
+ <para>
+ This form moves the session variable into another schema.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
+ <para>
+ Only the owner or a superuser is allowed to alter a session variable.
+ In order to move a session variable from one schema to another, the user
+ must also have the <literal>CREATE</literal> privilege on the new schema (or
+ be a superuser).
+
+ In order to move the session variable ownership from one role to another,
+ the user must also be a direct or indirect member of the new
+ owning role, and that role must have the <literal>CREATE</literal> privilege
+ on the session variable's schema (or be a superuser). These restrictions
+ enforce that altering the owner doesn't do anything you couldn't do by
+ dropping and recreating the session variable.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <para>
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (possibly schema-qualified) of the existing session variable
+ to alter.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_name</replaceable></term>
+ <listitem>
+ <para>
+ The new name for the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_owner</replaceable></term>
+ <listitem>
+ <para>
+ The user name of the new owner of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_schema</replaceable></term>
+ <listitem>
+ <para>
+ The new schema for the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To rename a session variable:
+<programlisting>
+ALTER VARIABLE foo RENAME TO boo;
+</programlisting>
+ </para>
+
+ <para>
+ To change the owner of the session variable <literal>boo</literal> to
+ <literal>joe</literal>:
+<programlisting>
+ALTER VARIABLE boo OWNER TO joe;
+</programlisting>
+ </para>
+
+ <para>
+ To change the schema of the session variable <literal>boo</literal> to
+ <literal>private</literal>:
+<programlisting>
+ALTER VARIABLE boo SET SCHEMA private;
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ Session variables and this command in particular are a PostgreSQL extension.
+ </para>
+ </refsect1>
+
+ <refsect1 id="sql-altervariable-see-also">
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 23d9029af9..50bfec6f28 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -65,6 +65,7 @@ COMMENT ON
TRANSFORM FOR <replaceable>type_name</replaceable> LANGUAGE <replaceable>lang_name</replaceable> |
TRIGGER <replaceable class="parameter">trigger_name</replaceable> ON <replaceable class="parameter">table_name</replaceable> |
TYPE <replaceable class="parameter">object_name</replaceable> |
+ VARIABLE <replaceable class="parameter">object_name</replaceable> |
VIEW <replaceable class="parameter">object_name</replaceable>
} IS '<replaceable class="parameter">text</replaceable>'
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
new file mode 100644
index 0000000000..70c87968ce
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,214 @@
+<!--
+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 [ { TEMPORARY | TEMP } ] [ IMMUTABLE ] VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceable class="parameter">collation</replaceable> ]
+ [ NOT NULL ] [ DEFAULT <replaceable class="parameter">default_expr</replaceable> ] [ { ON COMMIT DROP | ON TRANSACTION END RESET } ]
+</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command creates a session variable.
+ Session variables, like relations, exist within a schema and their access is
+ controlled via <command>GRANT</command> and <command>REVOKE</command>
+ commands.
+ </para>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a session variable's value returns either a NULL or a default value, unless
+ its value is set to something else in the current session with a LET
+ command. The content of a session variable is not transactional. This is the
+ same as regular variables in PL languages.
+ </para>
+
+ <para>
+ Session variables are retrieved by the <command>SELECT</command> SQL
+ command. Their value is set with the <command>LET</command> SQL command.
+ While session variables share properties with tables, their value cannot be
+ changed with an <command>UPDATE</command> command.
+ </para>
+
+ <note>
+ <para>
+ Inside a query or an expression, the session variable can be shadowed by
+ column or by routine's variable or routine argument. Such collisions of
+ identifiers can be resolved by using qualified identifiers. Session variables
+ can use schema name, columns can use table aliases, routine variables
+ can use block labels, and routine arguments can use the routine name.
+ </para>
+ </note>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry id="sql-createvariable-immutable">
+ <term><literal>IMMUTABLE</literal></term>
+ <listitem>
+ <para>
+ The assigned value of the session variable can not be changed.
+ Only if the session variable doesn't have a default value, a single
+ initialization is allowed using the <command>LET</command> command. Once
+ done, no further change is allowed until end of transaction
+ if the session variable was created with clause <literal>ON TRANSACTION
+ END RESET</literal>, or until reset of all session variables by
+ <command>DISCARD VARIABLES</command>, or until reset of all session
+ objects by command <command>DISCARD ALL</command>.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <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>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">data_type</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the data type of the session
+ variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>COLLATE <replaceable>collation</replaceable></literal></term>
+ <listitem>
+ <para>
+ The <literal>COLLATE</literal> clause assigns a collation to the session
+ variable (which must be of a collatable data type). If not specified,
+ the data type's default collation is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>NOT NULL</literal></term>
+ <listitem>
+ <para>
+ The <literal>NOT NULL</literal> clause forbids setting the session
+ variable to a null value. A session variable created as NOT NULL and
+ without an explicitly declared default value cannot be read until it is
+ initialized by a LET command. This requires the user to explicitly
+ initialize the session variable content before reading it, otherwise an
+ error will be thrown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DEFAULT <replaceable>default_expr</replaceable></literal></term>
+ <listitem>
+ <para>
+ The <literal>DEFAULT</literal> clause can be used to assign a default
+ value to a session variable. This expression is evaluated when the session
+ variable is first accessed for reading and had not yet been assigned a value.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ON COMMIT DROP</literal>, <literal>ON TRANSACTION END RESET</literal></term>
+ <listitem>
+ <para>
+ The <literal>ON COMMIT DROP</literal> clause specifies the behaviour of a
+ temporary session variable at transaction commit. With this clause, the
+ session variable is dropped at commit time. The clause is only allowed
+ for temporary variables. The <literal>ON TRANSACTION END RESET</literal>
+ clause causes the session variable to be reset to its default value when
+ the transaction is committed or rolled back.
+ </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 VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT var1;
+</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-altervariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index bf44c523ca..6f90672afa 100644
--- a/doc/src/sgml/ref/discard.sgml
+++ b/doc/src/sgml/ref/discard.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
+DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP | VARIABLES }
</synopsis>
</refsynopsisdiv>
@@ -75,6 +75,17 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>VARIABLES</literal></term>
+ <listitem>
+ <para>
+ Resets the value of all session variables. If a variable
+ is later reused, it is re-initialized to either
+ <literal>NULL</literal> or its default value.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>ALL</literal></term>
<listitem>
@@ -93,6 +104,7 @@ SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;
DISCARD SEQUENCES;
+DISCARD VARIABLES;
</programlisting></para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
new file mode 100644
index 0000000000..67988b5fcd
--- /dev/null
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -0,0 +1,118 @@
+<!--
+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 [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
+</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><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>
+ <para>
+ The name, optionally schema-qualified, of a session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>CASCADE</literal></term>
+ <listitem>
+ <para>
+ Automatically drop objects that depend on the session variable (such as
+ views), and in turn all objects that depend on those objects
+ (see <xref linkend="ddl-depend"/>).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESTRICT</literal></term>
+ <listitem>
+ <para>
+ Refuse to drop the session variable if any objects depend on it. This is
+ the default.
+ </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-altervariable"/></member>
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-let"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index dea19cd348..e2191b7767 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -108,6 +108,15 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
+
+GRANT { SELECT | UPDATE | ALL [ PRIVILEGES ] }
+ ON VARIABLE <replaceable>variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
+
+ TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
+
</synopsis>
</refsynopsisdiv>
diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml
new file mode 100644
index 0000000000..c5d1e4d962
--- /dev/null
+++ b/doc/src/sgml/ref/let.sgml
@@ -0,0 +1,109 @@
+<!--
+doc/src/sgml/ref/let.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-let">
+ <indexterm zone="sql-let">
+ <primary>LET</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>changing</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>LET</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>LET</refname>
+ <refpurpose>change a session variable's value</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+LET <replaceable class="parameter">session_variable</replaceable> = <replaceable class="parameter">sql_expression</replaceable>
+LET <replaceable class="parameter">session_variable</replaceable> = DEFAULT
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>LET</command> command assigns a value to the specified session
+ variable.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>session_variable</literal></term>
+ <listitem>
+ <para>
+ The name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sql_expression</literal></term>
+ <listitem>
+ <para>
+ An SQL expression (can be subquery in parenthesis). The result must
+ be of castable to the same data type as the session variable (in
+ implicit or assignment context).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DEFAULT</literal></term>
+ <listitem>
+ <para>
+ Reset the session variable to its default value, if that is defined.
+ If no explicit default value has been declared, the session variable
+ is set to NULL.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ Example:
+<programlisting>
+CREATE VARIABLE myvar AS integer;
+LET myvar = 10;
+LET myvar = (SELECT sum(val) FROM tab);
+LET myvar = DEFAULT;
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>LET</command> is a <productname>PostgreSQL</productname>
+ extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-altervariable"/></member>
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 47bd7dbda0..39cb595647 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -106,6 +106,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-A <replaceable class="parameter">session_variable</replaceable></option></term>
+ <term><option>--variable=<replaceable class="parameter">session_variable</replaceable></option></term>
+ <listitem>
+ <para>
+ Restore a named session variable only. Multiple session variables may
+ be specified with multiple <option>-A</option> switches.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-c</option></term>
<term><option>--clean</option></term>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 4fd4bfb3d7..0579b4599d 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -137,6 +137,13 @@ REVOKE [ { ADMIN | INHERIT } OPTION FOR ]
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON VARIABLE <replaceable>variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index a3b743e8c1..f5ec3c987b 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -75,6 +75,7 @@
&alterType;
&alterUser;
&alterUserMapping;
+ &alterVariable;
&alterView;
&analyze;
&begin;
@@ -127,6 +128,7 @@
&createType;
&createUser;
&createUserMapping;
+ &createVariable;
&createView;
&deallocate;
&declare;
@@ -175,6 +177,7 @@
&dropType;
&dropUser;
&dropUserMapping;
+ &dropVariable;
&dropView;
&end;
&execute;
@@ -183,6 +186,7 @@
&grant;
&importForeignSchema;
&insert;
+ &let;
&listen;
&load;
&lock;
--
2.37.0
* [PATCH] Documentation
@ 2025-12-18 07:31 Mikael Gustavsson <mikael.gustavsson@smhi.se>
2025-12-18 07:51 ` Re: [PATCH] Documentation Andreas Karlsson <andreas@proxel.se>
2025-12-19 08:53 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2025-12-19 17:01 ` Re: [PATCH] Documentation David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Mikael Gustavsson @ 2025-12-18 07:31 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Hi
This is my first ever patch to postgresql so please guide me if i'm doing something wrong.
This patch updates some examples in documentation client-auth.sgml from md5 to scram-sha-256
Reference: https://www.postgresql.org/message-id/176595607507.978865.11597773194269211255%40wrigleys.postgresql...
KR
Mikael Gustavsson, SMHI
Attachments:
DOC-update-client-auth-examples.patch (text/x-patch, 1.5K) download
diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml
index eb795bb0f21..a347ee18980 100644
--- a/doc/src/sgml/client-auth.sgml
+++ b/doc/src/sgml/client-auth.sgml
@@ -889,16 +889,16 @@ host all all 192.168.0.0/16 ident map=omicro
# list of names of administrators. Passwords are required in all cases.
#
# TYPE DATABASE USER ADDRESS METHOD
-local sameuser all md5
-local all /^.*helpdesk$ md5
-local all @admins md5
-local all +support md5
+local sameuser all scram-sha-256
+local all /^.*helpdesk$ scram-sha-256
+local all @admins scram-sha-256
+local all +support scram-sha-256
# The last two lines above can be combined into a single line:
-local all @admins,+support md5
+local all @admins,+support scram-sha-256
# The database column can also use lists and file names:
-local db1,db2,@demodbs all md5
+local db1,db2,@demodbs all scram-sha-256
</programlisting>
</example>
</sect1>
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
@ 2025-12-18 07:51 ` Andreas Karlsson <andreas@proxel.se>
2025-12-18 08:47 ` Re: [PATCH] Documentation Laurenz Albe <laurenz.albe@cybertec.at>
2025-12-18 10:05 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2 siblings, 2 replies; 12+ messages in thread
From: Andreas Karlsson @ 2025-12-18 07:51 UTC (permalink / raw)
To: Mikael Gustavsson <mikael.gustavsson@smhi.se>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On 12/18/25 8:31 AM, Mikael Gustavsson wrote:
> This is my first ever patch to postgresql so please guide me if i'm
> doing something wrong.
>
> This patch updates some examples in documentation client-auth.sgml from
> md5 to scram-sha-256
> Reference: https://www.postgresql.org/message-
> id/176595607507.978865.11597773194269211255%40wrigleys.postgresql.org
> <https://www.postgresql.org/message-
> id/176595607507.978865.11597773194269211255%40wrigleys.postgresql.org>
Välkommen!
The patch looks good to me and it looks like a useful change. To make
sure it is not lost you can add it to the current open commitfest but it
is also possible that some committer will just pick up the patch directly.
You can add it here: https://commitfest.postgresql.org/
I personally usually use "git format-patch" when submitting to the
mailing list but this format works just as well if it is just one simple
patch like this and not a series of multiple patches.
Thanks for the patch!
Andreas
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
2025-12-18 07:51 ` Re: [PATCH] Documentation Andreas Karlsson <andreas@proxel.se>
@ 2025-12-18 08:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 12+ messages in thread
From: Laurenz Albe @ 2025-12-18 08:47 UTC (permalink / raw)
To: Andreas Karlsson <andreas@proxel.se>; Mikael Gustavsson <mikael.gustavsson@smhi.se>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Thu, 2025-12-18 at 08:51 +0100, Andreas Karlsson wrote:
> On 12/18/25 8:31 AM, Mikael Gustavsson wrote:
> > This is my first ever patch to postgresql so please guide me if i'm
> > doing something wrong.
> >
> > This patch updates some examples in documentation client-auth.sgml from
> > md5 to scram-sha-256
> > Reference: https://www.postgresql.org/message-
> > id/176595607507.978865.11597773194269211255%40wrigleys.postgresql.org
> > <https://www.postgresql.org/message-
> > id/176595607507.978865.11597773194269211255%40wrigleys.postgresql.org>
>
> The patch looks good to me and it looks like a useful change.
+1
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
2025-12-18 07:51 ` Re: [PATCH] Documentation Andreas Karlsson <andreas@proxel.se>
@ 2025-12-18 10:05 ` John Naylor <johncnaylorls@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: John Naylor @ 2025-12-18 10:05 UTC (permalink / raw)
To: Andreas Karlsson <andreas@proxel.se>; +Cc: Mikael Gustavsson <mikael.gustavsson@smhi.se>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Thu, Dec 18, 2025 at 2:51 PM Andreas Karlsson <andreas@proxel.se> wrote:
> The patch looks good to me and it looks like a useful change. To make
> sure it is not lost you can add it to the current open commitfest but it
> is also possible that some committer will just pick up the patch directly.
I can commit this tomorrow.
Deprecating md5 came into v18, so even though this is not a
correction, we could backpatch this to v18 for consistency. Anyone
want to make a case for or against?
--
John Naylor
Amazon Web Services
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
@ 2025-12-19 08:53 ` John Naylor <johncnaylorls@gmail.com>
2025-12-19 15:14 ` Re: [PATCH] Documentation Fujii Masao <masao.fujii@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: John Naylor @ 2025-12-19 08:53 UTC (permalink / raw)
To: Mikael Gustavsson <mikael.gustavsson@smhi.se>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Thu, Dec 18, 2025 at 2:31 PM Mikael Gustavsson
<mikael.gustavsson@smhi.se> wrote:
> This patch updates some examples in documentation client-auth.sgml from md5 to scram-sha-256
> Reference: https://www.postgresql.org/message-id/176595607507.978865.11597773194269211255%40wrigleys.postgresql...
Pushed and backpatched to PG18, thanks!
--
John Naylor
Amazon Web Services
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
2025-12-19 08:53 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
@ 2025-12-19 15:14 ` Fujii Masao <masao.fujii@gmail.com>
2025-12-19 16:00 ` Re: [PATCH] Documentation Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 1 reply; 12+ messages in thread
From: Fujii Masao @ 2025-12-19 15:14 UTC (permalink / raw)
To: John Naylor <johncnaylorls@gmail.com>; +Cc: Mikael Gustavsson <mikael.gustavsson@smhi.se>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Fri, Dec 19, 2025 at 5:53 PM John Naylor <johncnaylorls@gmail.com> wrote:
>
> On Thu, Dec 18, 2025 at 2:31 PM Mikael Gustavsson
> <mikael.gustavsson@smhi.se> wrote:
> > This patch updates some examples in documentation client-auth.sgml from md5 to scram-sha-256
> > Reference: https://www.postgresql.org/message-id/176595607507.978865.11597773194269211255%40wrigleys.postgresql...
>
> Pushed and backpatched to PG18, thanks!
This change made me wonder whether we should also update the
pg_hba.conf examples
in high-availability.sgml and logical-replication.sgml. I've attached
a patch for that. Thoughts?
Regards,
--
Fujii Masao
Attachments:
v2-0001-Update-pg_hba.conf-example-to-reflect-MD5-depreca.patch (application/octet-stream, 1.5K) download
From 6155c097a3c29e1983c100e82836bb00af49be6f Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Sat, 20 Dec 2025 00:08:00 +0900
Subject: [PATCH v2] Update pg_hba.conf example to reflect MD5 deprecation,
take 2.
---
doc/src/sgml/high-availability.sgml | 2 +-
doc/src/sgml/logical-replication.sgml | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index 81eeadd6c47..b512b424c1c 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -857,7 +857,7 @@ archive_cleanup_command = 'pg_archivecleanup /path/to/archive "%r"'
# as a replication standby if the user's password is correctly supplied.
#
# TYPE DATABASE USER ADDRESS METHOD
-host replication foo 192.168.1.100/32 md5
+host replication foo 192.168.1.100/32 scram-sha-256
</programlisting>
</para>
<para>
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index aa013f348d4..c87a7b50cc4 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -3518,7 +3518,7 @@ wal_level = logical
(the values here depend on your actual network configuration and user you
want to use for connecting):
<programlisting>
-host all repuser 0.0.0.0/0 md5
+host all repuser 0.0.0.0/0 scram-sha-256
</programlisting>
</para>
--
2.51.2
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
2025-12-19 08:53 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2025-12-19 15:14 ` Re: [PATCH] Documentation Fujii Masao <masao.fujii@gmail.com>
@ 2025-12-19 16:00 ` Heikki Linnakangas <hlinnaka@iki.fi>
2026-01-05 06:39 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Heikki Linnakangas @ 2025-12-19 16:00 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; John Naylor <johncnaylorls@gmail.com>; +Cc: Mikael Gustavsson <mikael.gustavsson@smhi.se>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On 19/12/2025 17:14, Fujii Masao wrote:
> On Fri, Dec 19, 2025 at 5:53 PM John Naylor <johncnaylorls@gmail.com> wrote:
>>
>> On Thu, Dec 18, 2025 at 2:31 PM Mikael Gustavsson
>> <mikael.gustavsson@smhi.se> wrote:
>>> This patch updates some examples in documentation client-auth.sgml from md5 to scram-sha-256
>>> Reference: https://www.postgresql.org/message-id/176595607507.978865.11597773194269211255%40wrigleys.postgresql...
>>
>> Pushed and backpatched to PG18, thanks!
>
> This change made me wonder whether we should also update the
> pg_hba.conf examples
> in high-availability.sgml and logical-replication.sgml. I've attached
> a patch for that. Thoughts?
+1
Let's update this example in createuser.sgml too while we're at it:
> <para>
> To create the user <literal>joe</literal> as a superuser,
> and assign a password immediately:
> <screen>
> <prompt>$ </prompt><userinput>createuser -P -s -e joe</userinput>
> <computeroutput>Enter password for new role: </computeroutput><userinput>xyzzy</userinput>
> <computeroutput>Enter it again: </computeroutput><userinput>xyzzy</userinput>
> <computeroutput>CREATE ROLE joe PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;</computeroutput>
> </screen>
> In the above example, the new password isn't actually echoed when typed,
> but we show what was typed for clarity. As you see, the password is
> encrypted before it is sent to the client.
> </para>
I get this output for that command now:
$ createuser -P -s -e joe
Enter password for new role:
Enter it again:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE joe PASSWORD
'SCRAM-SHA-256$4096:vrJL1JVwK9VyDQ+XmGk2lg==$K32pX1bdFx3J+LeBcFpUOmaBnIUIduAexL+ufLYz/MI=:O6gxeGemIC3wbqEWMZXKAMOnQ5A1hM07nEu7KeSrEiE='
SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS;
- Heikki
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
2025-12-19 08:53 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2025-12-19 15:14 ` Re: [PATCH] Documentation Fujii Masao <masao.fujii@gmail.com>
2025-12-19 16:00 ` Re: [PATCH] Documentation Heikki Linnakangas <hlinnaka@iki.fi>
@ 2026-01-05 06:39 ` John Naylor <johncnaylorls@gmail.com>
2026-01-07 09:17 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: John Naylor @ 2026-01-05 06:39 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Fujii Masao <masao.fujii@gmail.com>; Mikael Gustavsson <mikael.gustavsson@smhi.se>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Fri, Dec 19, 2025 at 11:00 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
> On 19/12/2025 17:14, Fujii Masao wrote:
> > On Fri, Dec 19, 2025 at 5:53 PM John Naylor <johncnaylorls@gmail.com> wrote:
> >>
> >> On Thu, Dec 18, 2025 at 2:31 PM Mikael Gustavsson
> >> <mikael.gustavsson@smhi.se> wrote:
> >>> This patch updates some examples in documentation client-auth.sgml from md5 to scram-sha-256
> >>> Reference: https://www.postgresql.org/message-id/176595607507.978865.11597773194269211255%40wrigleys.postgresql...
> >>
> >> Pushed and backpatched to PG18, thanks!
> >
> > This change made me wonder whether we should also update the
> > pg_hba.conf examples
> > in high-availability.sgml and logical-replication.sgml. I've attached
> > a patch for that. Thoughts?
Sorry, I neglected to look around for similar places to update. I
don't see any other such places, so I will commit this and backpatch
to PG18 as well.
> Let's update this example in createuser.sgml too while we're at it:
> > <computeroutput>CREATE ROLE joe PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;</computeroutput>
> I get this output for that command now:
> SELECT pg_catalog.set_config('search_path', '', false);
> CREATE ROLE joe PASSWORD
> 'SCRAM-SHA-256$4096:vrJL1JVwK9VyDQ+XmGk2lg==$K32pX1bdFx3J+LeBcFpUOmaBnIUIduAexL+ufLYz/MI=:O6gxeGemIC3wbqEWMZXKAMOnQ5A1hM07nEu7KeSrEiE='
> SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS;
+1
This will need to be backpatched to PG14. Unlike the md5 case, the
encrypted password will not be reproducible. Since the above omission
hasn't been noticed, I don't suppose we need to call out this fact?
--
John Naylor
Amazon Web Services
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
2025-12-19 08:53 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2025-12-19 15:14 ` Re: [PATCH] Documentation Fujii Masao <masao.fujii@gmail.com>
2025-12-19 16:00 ` Re: [PATCH] Documentation Heikki Linnakangas <hlinnaka@iki.fi>
2026-01-05 06:39 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
@ 2026-01-07 09:17 ` John Naylor <johncnaylorls@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: John Naylor @ 2026-01-07 09:17 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Fujii Masao <masao.fujii@gmail.com>; Mikael Gustavsson <mikael.gustavsson@smhi.se>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Mon, Jan 5, 2026 at 1:39 PM John Naylor <johncnaylorls@gmail.com> wrote:
>
> On Fri, Dec 19, 2025 at 11:00 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> >
> > On 19/12/2025 17:14, Fujii Masao wrote:
> > > This change made me wonder whether we should also update the
> > > pg_hba.conf examples
> > > in high-availability.sgml and logical-replication.sgml. I've attached
> > > a patch for that. Thoughts?
>
> Sorry, I neglected to look around for similar places to update. I
> don't see any other such places, so I will commit this and backpatch
> to PG18 as well.
I pushed your patch, thanks!
> > Let's update this example in createuser.sgml too while we're at it:
>
> > > <computeroutput>CREATE ROLE joe PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;</computeroutput>
> This will need to be backpatched to PG14. Unlike the md5 case, the
> encrypted password will not be reproducible. Since the above omission
> hasn't been noticed, I don't suppose we need to call out this fact?
I pushed a fix for this, including missing tokens for earlier branches
(and decided not to mention random salt for the example).
--
John Naylor
Amazon Web Services
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PATCH] Documentation
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
@ 2025-12-19 17:01 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2025-12-19 17:01 UTC (permalink / raw)
To: Mikael Gustavsson <mikael.gustavsson@smhi.se>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Thu, Dec 18, 2025 at 12:31 AM Mikael Gustavsson <
mikael.gustavsson@smhi.se> wrote:
> This is my first ever patch to postgresql so please guide me if i'm doing
> something wrong.
>
>
The subject line of an email should fairly precisely say what is being
changed in the proposed patch. The documentation is huge, writing just
that helps no one when this email is showing up in their mail reader list
of emails or the search results page.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-01-07 09:17 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-04 18:23 [PATCH 11/11] documentation okbob@github.com <okbob@github.com>
2022-04-04 18:23 [PATCH v20220922 13/13] documentation okbob@github.com <okbob@github.com>
2025-12-18 07:31 [PATCH] Documentation Mikael Gustavsson <mikael.gustavsson@smhi.se>
2025-12-18 07:51 ` Re: [PATCH] Documentation Andreas Karlsson <andreas@proxel.se>
2025-12-18 08:47 ` Re: [PATCH] Documentation Laurenz Albe <laurenz.albe@cybertec.at>
2025-12-18 10:05 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2025-12-19 08:53 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2025-12-19 15:14 ` Re: [PATCH] Documentation Fujii Masao <masao.fujii@gmail.com>
2025-12-19 16:00 ` Re: [PATCH] Documentation Heikki Linnakangas <hlinnaka@iki.fi>
2026-01-05 06:39 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2026-01-07 09:17 ` Re: [PATCH] Documentation John Naylor <johncnaylorls@gmail.com>
2025-12-19 17:01 ` Re: [PATCH] Documentation David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox