public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: David G. Johnston <david.g.johnston@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Pavel Golub <pavel@gf.microolap.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] proposal: schema variables
Date: Fri, 2 Feb 2018 17:48:05 -0700
Message-ID: <CAKFQuwa00-4HTujbnYNy_OdZ2OfjUO3AX4R91DbQ1wPBZegCfg@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRBfb-GTZSHSRVTpMzGr26-7e-_RmOmRpmuk+xuDTgC=mA@mail.gmail.com>
References: <CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ@mail.gmail.com>
<623395617.20171113141500@gf.microolap.com>
<CAFj8pRDdS7ViLBJ6eA93u=C_x15EBv2deiNQDGkBS=LNrjzLLw@mail.gmail.com>
<CAFj8pRBfb-GTZSHSRVTpMzGr26-7e-_RmOmRpmuk+xuDTgC=mA@mail.gmail.com>
I've done a non-compilation documentation review, the diff from the poc
patch and the diff from master are attached.
Comments are inter-twined in the patch in xml comment format; though I
reiterate (some of?) them below.
On Fri, Feb 2, 2018 at 3:06 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
> Hi
>
> I wrote proof concept of schema variables. The patch is not nice, but the
> functionality is almost complete (for scalars only) and can be good enough
> for playing with this concept.
>
> I recap a goals (the order is random):
>
> 1. feature like PL/SQL package variables (with similar content life cycle)
> 2. available from any PL used by PostgreSQL, data can be shared between
> different PL
> 3. possibility to store short life data in fast secured storage
>
The generic use of the word secure here bothers me. I'm taking it to be
"protected by grant/revoke"-based privileges; plus session-locality.
4. possibility to pass parameters and results to/from anonymous blocks
> 5. session variables with possibility to process static code check
>
What does "process static code check" means here?
> 6. multiple API available from different environments - SQL commands, SQL
> functions, internal functions
>
I made the public aspect of this explicit in the CREATE VARIABLE doc
(though as noted below it probably belongs in section II)
> 7. data are stored in binary form
>
Thoughts during my review:
There is, for me, a cognitive dissonance between "schema variable" and
"variable value" - I'm partial to the later. Since we use "setting" for
GUCs the term variable here hopefully wouldn't cause ambiguity...
I've noticed that we don't seem to have or enforce any policy on how to
communicate "SQL standards compatibility" to the user...
We are missing the ability to alter ownership (or at least its
undocumented), and if that brings into existing ALTER VARIABLE we should
probably add ALTER TYPE TO new_type USING (cast) for completeness.
Its left for the reader to presume that because these are schema
"relations" that namespace resolution via search_path works the same as any
other relation.
I think I've answered my own question regarding DISCARD in that "variables"
discards values while if TEMP is in effect all temp variables are dropped.
Examples abound though it doesn't feel like too much: but saying "The usage
is very simple:" before giving the example in the function section seems to
be outside of our general style. A better preamble than "An example:"
would be nice but the example is so simple I could not think of anything
worth writing.
Its worth considering how both:
https://www.postgresql.org/docs/10/static/ddl.html
and
https://www.postgresql.org/docs/10/static/queries.html
could be updated to incorporate the broad picture of schema variables, with
examples, and leave the reference (SQL and functions) sections mainly
relegated to syntax and reminders.
A moderate number of lines changed are for typos and minor grammar edits.
David J.
Attachments:
[application/octet-stream] schema-variables-poc--dgj-response-diff.patch (11.5K, 3-schema-variables-poc--dgj-response-diff.patch)
download | inline diff:
commit fb39d8d2dc798ddd44611e349dedc0a8d41b35c6
Author: David G. Johnston (DU) <davidj@dealeruplift.com>
Date: Sat Feb 3 00:11:56 2018 +0000
respose to poc
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5031cd4d70..36e5e482a7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15743,10 +15743,10 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
</sect1>
<sect1 id="functions-schemavar">
- <title>Functions for access to schema variables</title>
+ <title>Schema Variable Functions</title>
<indexterm zone="functions-schemavar">
- <primary>Functions for access to schema variables</primary>
+ <primary>Schema Variable Functions</primary>
<secondary>functions</secondary>
</indexterm>
@@ -15760,10 +15760,12 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
<para>
These functions allow reading and writing schema variables values.
+ If the schema variable referenced does not exist (created using <xref linkend="sql-createvariable"/>)
+ these functions will (do something...).
</para>
-
+<!-- I'm preferential to get_variable_value and set_variable_value -->
<table id="functions-schemavar-tab">
- <title>Functions for access to chema variables</title>
+ <title>Functions for access to schema variables</title>
<tgroup cols="4">
<thead>
<row>
@@ -15773,14 +15775,13 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
<entry>Description</entry>
</row>
</thead>
-
<tbody>
<row>
<entry><literal><function>get_schema_variable(<parameter>variable</parameter>, <parameter>expected type</parameter>)</function></literal></entry>
<entry><type>regclass</type>, <type>anyelement</type></entry>
<entry><type>anyelement</type></entry>
<entry>
- Returns value of schema variables coverted to expected type.
+ Returns value of schema variable converted to expected type.
</entry>
</row>
@@ -15789,16 +15790,16 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
<entry><type>regclass</type>, <type>anyelement</type></entry>
<entry><type>void</type></entry>
<entry>
- Set a value of schema variable. Value is converted to type of schema variable.
+ Sets the value of schema variable to value, after converting the input to the correct type.
</entry>
</row>
</tbody>
</tgroup>
</table>
-
+ An example:
<para>
- The usage is very simple:
+
<programlisting>
CREATE TEMP VARIABLE foo AS numeric;
SELECT set_schema_variable('foo', 345.445);
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
index 037fa087f5..c06b0e7517 100644
--- a/doc/src/sgml/ref/create_variable.sgml
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -16,7 +16,7 @@ PostgreSQL documentation
<refnamediv>
<refname>CREATE VARIABLE</refname>
- <refpurpose>define a new schema secure typed variable</refpurpose>
+ <refpurpose>define a new permissioned typed schema variable</refpurpose>
</refnamediv>
<refsynopsisdiv>
@@ -24,32 +24,39 @@ PostgreSQL documentation
CREATE VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ]
</synopsis>
</refsynopsisdiv>
+<!-- a multiple variable version of this might be useful...
+ name data_type [, name data_type] -->
<refsect1>
<title>Description</title>
<para>
<command>CREATE VARIABLE</command> creates a new schema variable.
- These variables are memory only non transactional, but typed and
- secure. The access is controlled by rights defined by command
- <command>GRANT</command> and command <command>REVOKE</command>.
+ These variables are scalar typed, non-transactional, and, like relations,
+ exist within a schema with access controlled via
+ <command>GRANT</command> and <command>REVOKE</command>.
</para>
<para>
- The schema variable is initialized to NULL value. The content of
- variable is lost when session is destroyed.
+ The value of a schema variable is session-local. Retrieving
+ a variable's value will return NULL unless its value has been set
+ to something else in the current session.
</para>
<para>
- The schema variable can be any scalar only.
- type.
+ Retrieval is done via the <function>get_schema_variable</function>dunxrion or the SQL
+ command <command>SELECT</command>. Setting of values is done via the
+ <function>set_schema_variable</function> function or the SQL command
+ <command>LET</command>.
+ Notably, while schema variables are in many ways a kind of table you cannot use
+ <command>UPDATE</command> on them.
</para>
<para>
- After a variable is created, you use the special functions
- <function>get_schema_variables</function>, <function>set_schema_variables</function>.
- type.
- </para>
+ For purposes of name uniqueness relation-like objects (e.g., tables, indexes)
+ within the same schema are considered. i.e., you cannot give a table and a
+ schema variable the same name. This is a consequence of them being treated
+ like relations for purposes of <command>SELECT</command>.
</refsect1>
<refsect1>
@@ -60,10 +67,9 @@ CREATE VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceab
<term><literal>IF NOT EXISTS</literal></term>
<listitem>
<para>
- Do not throw an error if a relation with the same name already exists.
- A notice is issued in this case. Note that there is no guarantee that
- the existing relation is anything like the variable that would have
- been created - it might not even be a variable.
+ Do not throw an error if the name already exists. A notice is issued in this case.
+ Note that type of the variable is not considered, nor could it be since the namespace
+ searched contains non-variable objects.
</para>
</listitem>
</varlistentry>
@@ -81,7 +87,7 @@ CREATE VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceab
<term><replaceable class="parameter">data_type</replaceable></term>
<listitem>
<para>
- The name (optionally schema-qualified) of the data type ofvariable to be created.
+ The name (optionally schema-qualified) of the data type of the variable to be created.
</para>
</listitem>
</varlistentry>
@@ -107,7 +113,7 @@ CREATE VARIABLE var1 AS integer;
</para>
<para>
- Set a value of this variable:
+ Set this variable's value; then retrieve it converted to numeric.
<programlisting>
CREATE VARIABLE
postgres=# select set_schema_variable('var1', 10);
@@ -129,7 +135,8 @@ postgres=# select get_schema_variable('var1', null::numeric);
<title>Compatibility</title>
<para>
- <command>CREATE VARIABLE</command> is PostgreSQL feature
+ <command>CREATE VARIABLE</command> is a PostgreSQL feature.
+ <!-- The choice of wording here seems to be left to personal preference... -->
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index b348c02e0b..395453bba0 100644
--- a/doc/src/sgml/ref/discard.sgml
+++ b/doc/src/sgml/ref/discard.sgml
@@ -79,7 +79,8 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP | VARIABLES}
<term><literal>VARIABLES</literal></term>
<listitem>
<para>
- Releases content of all schema variables in current session.
+ Sets the value of all schema variables to NULL.
+ <!-- What happens to temporary schema variables -->
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
index f6c2e46476..06130fd510 100644
--- a/doc/src/sgml/ref/drop_variable.sgml
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -29,8 +29,9 @@ DROP VARIABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [,
<title>Description</title>
<para>
- <command>DROP VARIABLE</command> removes schema variable.
+ <command>DROP VARIABLE</command> removes a schema variable.
A variable can only be dropped by its owner or a superuser.
+ <!-- this would suggest that we need an alter variable owner to command -->
</para>
</refsect1>
@@ -75,6 +76,8 @@ DROP VARIABLE var1;
<para>
<command>DROP VARIABLE</command> is proprietary PostgreSQL command.
+ <!-- create variable is a "PostgreSQL feature",
+ this is a "proprietary PostgreSQL command" ... -->
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 7dde54ce0f..006364ebe5 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -173,6 +173,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
foreign servers,
large objects,
schemas,
+ schema variables,
or tablespaces.
For other types of objects, the default privileges
granted to <literal>PUBLIC</literal> are as follows:
@@ -210,6 +211,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
For sequences, this privilege also allows the use of the
<function>currval</function> function.
For large objects, this privilege allows the object to be read.
+ For schema variables, this privilege allows the <function>get_schema_variable</function>
+ to read the variable's value.
</para>
</listitem>
</varlistentry>
@@ -245,6 +248,9 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
<function>setval</function> functions.
For large objects, this privilege allows writing or truncating the
object.
+ For schema variables, this privilege allows <command>LET</command>
+ and <function>set_schema_variable</function> to modify the schema variable's
+ value.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml
index b040b5e1fe..e8bf3f6dd4 100644
--- a/doc/src/sgml/ref/let.sgml
+++ b/doc/src/sgml/ref/let.sgml
@@ -16,7 +16,7 @@ PostgreSQL documentation
<refnamediv>
<refname>LET</refname>
- <refpurpose>change a schema variable</refpurpose>
+ <refpurpose>change a schema variable's value</refpurpose>
</refnamediv>
<refsynopsisdiv>
@@ -29,7 +29,7 @@ LET <replaceable class="parameter">schema_variable</replaceable> = <replaceable
<title>Description</title>
<para>
- The <command>LET</command> command sets specified schema variable.
+ The <command>LET</command> command updates the specified schema variable' value.
</para>
</refsect1>
@@ -42,7 +42,7 @@ LET <replaceable class="parameter">schema_variable</replaceable> = <replaceable
<term><literal>schema_variable</literal></term>
<listitem>
<para>
- Specifies that the name of schema variable.
+ The name of schema variable.
</para>
</listitem>
</varlistentry>
@@ -51,7 +51,7 @@ LET <replaceable class="parameter">schema_variable</replaceable> = <replaceable
<term><literal>sql expression</literal></term>
<listitem>
<para>
- Any SQL expression.
+ An SQL expression, the result is cast to the schema variable's type.
</para>
</listitem>
</varlistentry>
@@ -71,6 +71,8 @@ LET myvar = (SELECT sum(val) FROM tab);
<title>Compatibility</title>
<para>
+ <!-- this feels like it needs to be more specific,
+ but I don't know enough to make it so -->
<literal>LET</literal> extends syntax defined in the SQL
standard. The standard knows <literal>SET</literal> command,
that is used for different purpouse in PostgreSQL.
[application/octet-stream] schema-variables-poc--dgj-response-full.patch (120.6K, 4-schema-variables-poc--dgj-response-full.patch)
download | inline diff:
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 71e20f2740..fbf78e602d 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1813,7 +1813,8 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<literal>m</literal> = materialized view,
<literal>c</literal> = composite type,
<literal>f</literal> = foreign table,
- <literal>p</literal> = partitioned table
+ <literal>p</literal> = partitioned table,
+ <literal>V</literal> = schema variable
</entry>
</row>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 487c7ff750..36e5e482a7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15742,6 +15742,83 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
</sect1>
+ <sect1 id="functions-schemavar">
+ <title>Schema Variable Functions</title>
+
+ <indexterm zone="functions-schemavar">
+ <primary>Schema Variable Functions</primary>
+ <secondary>functions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>get_schema_variable</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>set_schema_variable</primary>
+ </indexterm>
+
+ <para>
+ These functions allow reading and writing schema variables values.
+ If the schema variable referenced does not exist (created using <xref linkend="sql-createvariable"/>)
+ these functions will (do something...).
+ </para>
+<!-- I'm preferential to get_variable_value and set_variable_value -->
+ <table id="functions-schemavar-tab">
+ <title>Functions for access to schema variables</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Argument Type</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal><function>get_schema_variable(<parameter>variable</parameter>, <parameter>expected type</parameter>)</function></literal></entry>
+ <entry><type>regclass</type>, <type>anyelement</type></entry>
+ <entry><type>anyelement</type></entry>
+ <entry>
+ Returns value of schema variable converted to expected type.
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal><function>set_schema_variable(<parameter>variable</parameter>, <parameter>value</parameter>)</function></literal></entry>
+ <entry><type>regclass</type>, <type>anyelement</type></entry>
+ <entry><type>void</type></entry>
+ <entry>
+ Sets the value of schema variable to value, after converting the input to the correct type.
+ </entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+ An example:
+ <para>
+
+<programlisting>
+CREATE TEMP VARIABLE foo AS numeric;
+SELECT set_schema_variable('foo', 345.445);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('foo', null::numeric);
+
+ get_schema_variable
+---------------------
+ 345.445
+(1 row)
+</programlisting>
+ </para>
+
+ </sect1>
+
<sect1 id="functions-info">
<title>System Information Functions</title>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 22e6893211..1d34f72bdd 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -99,6 +99,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY createType SYSTEM "create_type.sgml">
<!ENTITY createUser SYSTEM "create_user.sgml">
<!ENTITY createUserMapping SYSTEM "create_user_mapping.sgml">
+<!ENTITY createVariable SYSTEM "create_variable.sgml">
<!ENTITY createView SYSTEM "create_view.sgml">
<!ENTITY deallocate SYSTEM "deallocate.sgml">
<!ENTITY declare SYSTEM "declare.sgml">
@@ -147,6 +148,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY dropType SYSTEM "drop_type.sgml">
<!ENTITY dropUser SYSTEM "drop_user.sgml">
<!ENTITY dropUserMapping SYSTEM "drop_user_mapping.sgml">
+<!ENTITY dropVariable SYSTEM "drop_variable.sgml">
<!ENTITY dropView SYSTEM "drop_view.sgml">
<!ENTITY end SYSTEM "end.sgml">
<!ENTITY execute SYSTEM "execute.sgml">
@@ -155,6 +157,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/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
new file mode 100644
index 0000000000..c06b0e7517
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,151 @@
+<!--
+doc/src/sgml/ref/create_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-createvariable">
+ <indexterm zone="sql-createvariable">
+ <primary>CREATE VARIABLE</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>CREATE VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE VARIABLE</refname>
+ <refpurpose>define a new permissioned typed schema variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ]
+</synopsis>
+ </refsynopsisdiv>
+<!-- a multiple variable version of this might be useful...
+ name data_type [, name data_type] -->
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>CREATE VARIABLE</command> creates a new schema variable.
+ These variables are scalar typed, non-transactional, and, like relations,
+ exist within a schema with access controlled via
+ <command>GRANT</command> and <command>REVOKE</command>.
+ </para>
+
+ <para>
+ The value of a schema variable is session-local. Retrieving
+ a variable's value will return NULL unless its value has been set
+ to something else in the current session.
+ </para>
+
+ <para>
+ Retrieval is done via the <function>get_schema_variable</function>dunxrion or the SQL
+ command <command>SELECT</command>. Setting of values is done via the
+ <function>set_schema_variable</function> function or the SQL command
+ <command>LET</command>.
+ Notably, while schema variables are in many ways a kind of table you cannot use
+ <command>UPDATE</command> on them.
+ </para>
+
+ <para>
+ For purposes of name uniqueness relation-like objects (e.g., tables, indexes)
+ within the same schema are considered. i.e., you cannot give a table and a
+ schema variable the same name. This is a consequence of them being treated
+ like relations for purposes of <command>SELECT</command>.
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <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.
+ Note that type of the variable is not considered, nor could it be since the namespace
+ searched contains non-variable objects.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the variable to be created.
+ </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 variable to be created.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Use <command>DROP VARIABLE</command> to remove a variable.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Create an integer variable <literal>var1</literal>:
+<programlisting>
+CREATE VARIABLE var1 AS integer;
+</programlisting>
+ </para>
+
+ <para>
+ Set this variable's value; then retrieve it converted to numeric.
+<programlisting>
+CREATE VARIABLE
+postgres=# select set_schema_variable('var1', 10);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+postgres=# select get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 10
+(1 row)
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>CREATE VARIABLE</command> is a PostgreSQL feature.
+ <!-- The choice of wording here seems to be left to personal preference... -->
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index 6b909b7232..395453bba0 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>
@@ -76,6 +76,16 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
</varlistentry>
<varlistentry>
+ <term><literal>VARIABLES</literal></term>
+ <listitem>
+ <para>
+ Sets the value of all schema variables to NULL.
+ <!-- What happens to temporary schema variables -->
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>ALL</literal></term>
<listitem>
<para>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
new file mode 100644
index 0000000000..06130fd510
--- /dev/null
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -0,0 +1,92 @@
+<!--
+doc/src/sgml/ref/drop_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-dropvariable">
+ <indexterm zone="sql-dropvariable">
+ <primary>DROP VARIABLE</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>DROP VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP VARIABLE</refname>
+ <refpurpose>remove a schema 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 schema variable.
+ A variable can only be dropped by its owner or a superuser.
+ <!-- this would suggest that we need an alter variable owner to command -->
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the 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 schema variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To remove the schema variable <literal>var1</literal>:
+
+<programlisting>
+DROP VARIABLE var1;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>DROP VARIABLE</command> is proprietary PostgreSQL command.
+ <!-- create variable is a "PostgreSQL feature",
+ this is a "proprietary PostgreSQL command" ... -->
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index ff64c7a3ba..006364ebe5 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -79,6 +79,12 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE <replaceable>type_name</replaceable> [, ...]
TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
+GRANT { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON { VARIABLE <replaceable class="parameter">variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
+
<phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
[ GROUP ] <replaceable class="parameter">role_name</replaceable>
@@ -167,6 +173,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
foreign servers,
large objects,
schemas,
+ schema variables,
or tablespaces.
For other types of objects, the default privileges
granted to <literal>PUBLIC</literal> are as follows:
@@ -204,6 +211,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
For sequences, this privilege also allows the use of the
<function>currval</function> function.
For large objects, this privilege allows the object to be read.
+ For schema variables, this privilege allows the <function>get_schema_variable</function>
+ to read the variable's value.
</para>
</listitem>
</varlistentry>
@@ -239,6 +248,9 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
<function>setval</function> functions.
For large objects, this privilege allows writing or truncating the
object.
+ For schema variables, this privilege allows <command>LET</command>
+ and <function>set_schema_variable</function> to modify the schema variable's
+ value.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml
new file mode 100644
index 0000000000..e8bf3f6dd4
--- /dev/null
+++ b/doc/src/sgml/ref/let.sgml
@@ -0,0 +1,90 @@
+<!--
+doc/src/sgml/ref/let.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-let">
+ <indexterm zone="sql-let">
+ <primary>LET</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>LET</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>LET</refname>
+ <refpurpose>change a schema variable's value</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+LET <replaceable class="parameter">schema_variable</replaceable> = <replaceable class="parameter">sql_expression</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>LET</command> command updates the specified schema variable' value.
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>schema_variable</literal></term>
+ <listitem>
+ <para>
+ The name of schema variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sql expression</literal></term>
+ <listitem>
+ <para>
+ An SQL expression, the result is cast to the schema variable's type.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <para>
+ Example:
+<programlisting>
+CREATE VARIABLE myvar AS integer;
+LET myvar = 10;
+LET myvar = (SELECT sum(val) FROM tab);
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <!-- this feels like it needs to be more specific,
+ but I don't know enough to make it so -->
+ <literal>LET</literal> extends syntax defined in the SQL
+ standard. The standard knows <literal>SET</literal> command,
+ that is used for different purpouse in PostgreSQL.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 7018202f14..73778f01f9 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -108,6 +108,14 @@ REVOKE [ GRANT OPTION FOR ]
REVOKE [ ADMIN OPTION FOR ]
<replaceable class="parameter">role_name</replaceable> [, ...] FROM <replaceable class="parameter">role_name</replaceable> [, ...]
[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON { VARIABLE <replaceable class="parameter">variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable>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 d27fb414f7..b3f9fff511 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -127,6 +127,7 @@
&createType;
&createUser;
&createUserMapping;
+ &createVariable;
&createView;
&deallocate;
&declare;
@@ -175,6 +176,7 @@
&dropType;
&dropUser;
&dropUserMapping;
+ &dropVariable;
&dropView;
&end;
&execute;
@@ -183,6 +185,7 @@
&grant;
&importForeignSchema;
&insert;
+ &let;
&listen;
&load;
&lock;
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 1156627b9e..268534ea87 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -284,6 +284,9 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs,
case OBJECT_TYPE:
whole_mask = ACL_ALL_RIGHTS_TYPE;
break;
+ case OBJECT_VARIABLE:
+ whole_mask = ACL_ALL_RIGHTS_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized object type: %d", objtype);
/* not reached, but keep compiler quiet */
@@ -506,6 +509,10 @@ ExecuteGrantStmt(GrantStmt *stmt)
all_privileges = ACL_ALL_RIGHTS_FOREIGN_SERVER;
errormsg = gettext_noop("invalid privilege type %s for foreign server");
break;
+ case OBJECT_VARIABLE:
+ all_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ errormsg = gettext_noop("invalid privilege type %s for schema variable");
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) stmt->objtype);
@@ -576,6 +583,7 @@ ExecGrantStmt_oids(InternalGrant *istmt)
{
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
+ case OBJECT_VARIABLE:
ExecGrant_Relation(istmt);
break;
case OBJECT_DATABASE:
@@ -645,6 +653,7 @@ objectNamesToOids(ObjectType objtype, List *objnames)
{
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
+ case OBJECT_VARIABLE:
foreach(cell, objnames)
{
RangeVar *relvar = (RangeVar *) lfirst(cell);
@@ -1021,6 +1030,10 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s
all_privileges = ACL_ALL_RIGHTS_SCHEMA;
errormsg = gettext_noop("invalid privilege type %s for schema");
break;
+ case OBJECT_VARIABLE:
+ all_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ errormsg = gettext_noop("invalid privilege type %s for schema variable");
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) action->objtype);
@@ -1218,6 +1231,12 @@ SetDefaultACL(InternalDefaultACL *iacls)
this_privileges = ACL_ALL_RIGHTS_SCHEMA;
break;
+ case OBJECT_VARIABLE:
+ objtype = DEFACLOBJ_VARIABLE;
+ if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS)
+ this_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ break;
+
default:
elog(ERROR, "unrecognized objtype: %d",
(int) iacls->objtype);
@@ -1444,6 +1463,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid)
case DEFACLOBJ_NAMESPACE:
iacls.objtype = OBJECT_SCHEMA;
break;
+ case DEFACLOBJ_VARIABLE:
+ iacls.objtype = OBJECT_VARIABLE;
+ break;
default:
/* Shouldn't get here */
elog(ERROR, "unexpected default ACL type: %d",
@@ -3459,6 +3481,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_TYPE:
msg = gettext_noop("permission denied for type %s");
break;
+ case OBJECT_VARIABLE:
+ msg = gettext_noop("permission denied for schema variable %s");
+ break;
case OBJECT_VIEW:
msg = gettext_noop("permission denied for view %s");
break;
@@ -3569,6 +3594,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_TYPE:
msg = gettext_noop("must be owner of type %s");
break;
+ case OBJECT_VARIABLE:
+ msg = gettext_noop("must be owner of schema variable %s");
+ break;
case OBJECT_VIEW:
msg = gettext_noop("must be owner of view %s");
break;
@@ -3683,6 +3711,7 @@ pg_aclmask(ObjectType objtype, Oid table_oid, AttrNumber attnum, Oid roleid,
pg_attribute_aclmask(table_oid, attnum, roleid, mask, how);
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
+ case OBJECT_VARIABLE:
return pg_class_aclmask(table_oid, roleid, mask, how);
case OBJECT_DATABASE:
return pg_database_aclmask(table_oid, roleid, mask, how);
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 0f34f5381a..558e641d56 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -291,6 +291,7 @@ heap_create(const char *relname,
switch (relkind)
{
case RELKIND_VIEW:
+ case RELKIND_VARIABLE:
case RELKIND_COMPOSITE_TYPE:
case RELKIND_FOREIGN_TABLE:
case RELKIND_PARTITIONED_TABLE:
@@ -1067,7 +1068,9 @@ heap_create_with_catalog(const char *relname,
if (existing_relid != InvalidOid)
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
- errmsg("relation \"%s\" already exists", relname)));
+ errmsg("%s \"%s\" already exists",
+ relkind == RELKIND_VARIABLE ? "variable" : "relation",
+ relname)));
/*
* Since we are going to create a rowtype as well, also check for
@@ -1150,6 +1153,10 @@ heap_create_with_catalog(const char *relname,
relacl = get_user_default_acl(OBJECT_SEQUENCE, ownerid,
relnamespace);
break;
+ case RELKIND_VARIABLE:
+ relacl = get_user_default_acl(OBJECT_VARIABLE, ownerid,
+ relnamespace);
+ break;
default:
relacl = NULL;
break;
@@ -1181,7 +1188,8 @@ heap_create_with_catalog(const char *relname,
* Decide whether to create an array type over the relation's rowtype. We
* do not create any array types for system catalogs (ie, those made
* during initdb). We do not create them where the use of a relation as
- * such is an implementation detail: toast tables, sequences and indexes.
+ * such is an implementation detail: toast tables, sequences, indexes and
+ * variables.
*/
if (IsUnderPostmaster && (relkind == RELKIND_RELATION ||
relkind == RELKIND_VIEW ||
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 570e65affb..62479743c7 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -521,6 +521,9 @@ static const struct object_type_map
"sequence", OBJECT_SEQUENCE
},
{
+ "variable", OBJECT_VARIABLE
+ },
+ {
"toast table", -1
}, /* unmapped */
{
@@ -824,6 +827,7 @@ get_object_address(ObjectType objtype, Node *object,
case OBJECT_VIEW:
case OBJECT_MATVIEW:
case OBJECT_FOREIGN_TABLE:
+ case OBJECT_VARIABLE:
address =
get_relation_by_qualified_name(objtype, castNode(List, object),
&relation, lockmode,
@@ -1260,6 +1264,14 @@ get_relation_by_qualified_name(ObjectType objtype, List *object,
errmsg("\"%s\" is not a foreign table",
RelationGetRelationName(relation))));
break;
+ case OBJECT_VARIABLE:
+ if (relation->rd_rel->relkind != RELKIND_VARIABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a schema variable",
+ RelationGetRelationName(relation))));
+ break;
+
default:
elog(ERROR, "unrecognized objtype: %d", (int) objtype);
break;
@@ -1847,6 +1859,8 @@ get_object_address_defacl(List *object, bool missing_ok)
case DEFACLOBJ_NAMESPACE:
objtype_str = "schemas";
break;
+ case DEFACLOBJ_VARIABLE:
+ objtype_str = "variables";
default:
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -2109,6 +2123,7 @@ pg_get_object_address(PG_FUNCTION_ARGS)
{
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
+ case OBJECT_VARIABLE:
case OBJECT_VIEW:
case OBJECT_MATVIEW:
case OBJECT_INDEX:
@@ -2233,6 +2248,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
case OBJECT_INDEX:
case OBJECT_SEQUENCE:
case OBJECT_TABLE:
+ case OBJECT_VARIABLE:
case OBJECT_VIEW:
case OBJECT_MATVIEW:
case OBJECT_FOREIGN_TABLE:
@@ -3299,6 +3315,11 @@ getObjectDescription(const ObjectAddress *object)
_("default privileges on new schemas belonging to role %s"),
GetUserNameFromId(defacl->defaclrole, false));
break;
+ case DEFACLOBJ_VARIABLE:
+ appendStringInfo(&buffer,
+ _("default privileges on new schema variables belonging to role %s"),
+ GetUserNameFromId(defacl->defaclrole, false));
+ break;
default:
/* shouldn't get here */
appendStringInfo(&buffer,
@@ -3502,6 +3523,10 @@ getRelationDescription(StringInfo buffer, Oid relid)
appendStringInfo(buffer, _("sequence %s"),
relname);
break;
+ case RELKIND_VARIABLE:
+ appendStringInfo(buffer, _("variable %s"),
+ relname);
+ break;
case RELKIND_TOASTVALUE:
appendStringInfo(buffer, _("toast table %s"),
relname);
@@ -4830,6 +4855,10 @@ getObjectIdentityParts(const ObjectAddress *object,
appendStringInfoString(&buffer,
" on schemas");
break;
+ case DEFACLOBJ_VARIABLE:
+ appendStringInfoString(&buffer,
+ " on schema variables");
+ break;
}
if (objname)
@@ -5122,6 +5151,8 @@ get_relkind_objtype(char relkind)
return OBJECT_INDEX;
case RELKIND_SEQUENCE:
return OBJECT_SEQUENCE;
+ case RELKIND_VARIABLE:
+ return OBJECT_VARIABLE;
case RELKIND_VIEW:
return OBJECT_VIEW;
case RELKIND_MATVIEW:
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index 4a6c99e090..5747272c9a 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -18,7 +18,7 @@ OBJS = amcmds.o aggregatecmds.o alter.o analyze.o async.o cluster.o comment.o \
event_trigger.o explain.o extension.o foreigncmds.o functioncmds.o \
indexcmds.o lockcmds.o matview.o operatorcmds.o opclasscmds.o \
policy.o portalcmds.o prepare.o proclang.o publicationcmds.o \
- schemacmds.o seclabel.o sequence.o statscmds.o subscriptioncmds.o \
+ schemacmds.o schemavar.o seclabel.o sequence.o statscmds.o subscriptioncmds.o \
tablecmds.o tablespace.o trigger.o tsearchcmds.o typecmds.o user.o \
vacuum.o vacuumlazy.o variable.o view.o
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index b3933df9af..71e5aad852 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1484,6 +1484,9 @@ BeginCopy(ParseState *pstate,
Assert(query->utilityStmt == NULL);
+ /* Don't expect LET stmt here, is not possible to do write it */
+ Assert(query->commandType != CMD_LET);
+
/*
* Similarly the grammar doesn't enforce the presence of a RETURNING
* clause, but this is required here.
diff --git a/src/backend/commands/discard.c b/src/backend/commands/discard.c
index 353ec990af..33db47e634 100644
--- a/src/backend/commands/discard.c
+++ b/src/backend/commands/discard.c
@@ -18,6 +18,7 @@
#include "commands/async.h"
#include "commands/discard.h"
#include "commands/prepare.h"
+#include "commands/schemavar.h"
#include "commands/sequence.h"
#include "utils/guc.h"
#include "utils/portal.h"
@@ -25,7 +26,7 @@
static void DiscardAll(bool isTopLevel);
/*
- * DISCARD { ALL | SEQUENCES | TEMP | PLANS }
+ * DISCARD { ALL | SEQUENCES | TEMP | PLANS | VARIABLES}
*/
void
DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
@@ -48,6 +49,10 @@ DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
ResetTempTableNamespace();
break;
+ case DISCARD_VARIABLES:
+ ResetSchemaVariablesCache();
+ break;
+
default:
elog(ERROR, "unrecognized DISCARD target: %d", stmt->target);
}
@@ -75,4 +80,5 @@ DiscardAll(bool isTopLevel)
ResetPlanCache();
ResetTempTableNamespace();
ResetSequenceCaches();
+ ResetSchemaVariablesCache();
}
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 549c7ea51d..c8e2b822e1 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -126,6 +126,7 @@ static event_trigger_support_data event_trigger_support[] = {
{"TEXT SEARCH TEMPLATE", true},
{"TYPE", true},
{"USER MAPPING", true},
+ {"VARIABLE", true},
{"VIEW", true},
{NULL, false}
};
@@ -1124,6 +1125,7 @@ EventTriggerSupportsObjectType(ObjectType obtype)
case OBJECT_TSTEMPLATE:
case OBJECT_TYPE:
case OBJECT_USER_MAPPING:
+ case OBJECT_VARIABLE:
case OBJECT_VIEW:
return true;
@@ -2222,6 +2224,8 @@ stringify_grant_objtype(ObjectType objtype)
return "TABLESPACE";
case OBJECT_TYPE:
return "TYPE";
+ case OBJECT_VARIABLE:
+ return "VARIABLE";
/* these currently aren't used */
case OBJECT_ACCESS_METHOD:
case OBJECT_AGGREGATE:
@@ -2304,6 +2308,8 @@ stringify_adefprivs_objtype(ObjectType objtype)
return "TABLESPACES";
case OBJECT_TYPE:
return "TYPES";
+ case OBJECT_VARIABLE:
+ return "VARIABLES";
/* these currently aren't used */
case OBJECT_ACCESS_METHOD:
case OBJECT_AGGREGATE:
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 41cd47e8bc..11c8257fca 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -893,6 +893,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case CMD_DELETE:
pname = operation = "Delete";
break;
+ case CMD_LET:
+ pname = operation = "Let";
+ break;
default:
pname = "???";
break;
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index b945b1556a..a69471e926 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -151,6 +151,7 @@ PrepareQuery(PrepareStmt *stmt, const char *queryString,
case CMD_INSERT:
case CMD_UPDATE:
case CMD_DELETE:
+ case CMD_LET:
/* OK */
break;
default:
diff --git a/src/backend/commands/schemavar.c b/src/backend/commands/schemavar.c
new file mode 100644
index 0000000000..cb803fab0c
--- /dev/null
+++ b/src/backend/commands/schemavar.c
@@ -0,0 +1,663 @@
+/*-------------------------------------------------------------------------
+ *
+ * schemavar.c
+ * PostgreSQL session variable support code.
+ *
+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/commands/schemavar.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+#include "miscadmin.h"
+
+#include "access/htup_details.h"
+#include "access/xact.h"
+#include "catalog/objectaddress.h"
+#include "catalog/namespace.h"
+#include "catalog/pg_class.h"
+#include "catalog/pg_type.h"
+#include "commands/tablecmds.h"
+#include "commands/schemavar.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_type.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/hsearch.h"
+#include "utils/inval.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+#include "utils/syscache.h"
+
+/*
+ * This schema variable cache mixes the cache and storages behave. That is not
+ * correct and it is problematic, when variable is removed. The own storage
+ * based on storage manager can be implemented, RelFileNode can be defined and
+ * mechanism based on PendingRelDelete struct can be used. This is a argument
+ * for implementation schema variables based on pg_class.
+ * Alternative solution can be detection of schema changes and recheck at and
+ * of transaction.
+ */
+typedef struct SchemaVarData
+{
+ Oid varid; /* pg_class OID of this sequence (hash key) */
+ Oid typid; /* OID of the data type */
+ int32 typmod;
+ int16 typlen;
+ bool typbyval;
+ bool isnull;
+ bool freeval;
+ Datum value;
+} SchemaVarData;
+
+typedef SchemaVarData *SchemaVar;
+
+static HTAB *schemavarhashtab = NULL; /* hash table for session variables */
+static MemoryContext SchemaVarMemoryContext = NULL;
+
+static Datum datumCast(Datum value,
+ Oid target_typid, int target_typmod,
+ Oid source_typid, int source_typmod);
+
+static bool first_time = true;
+static bool cache_is_valid = true;
+
+static void InvalidateSchemaVarCacheCallback(Datum arg, int cacheid, uint32 hashvalue);
+
+/* just mark cache to recheck */
+static void
+InvalidateSchemaVarCacheCallback(Datum arg, int cacheid, uint32 hashvalue)
+{
+ /*
+ * because this cache holds values of schema variables, then
+ * the content cannot be removed in this momemt. We should to
+ * wait on transaction end.
+ */
+ cache_is_valid = false;
+}
+
+/*
+ * Wait on commit or rollback and clean values that miss entry in system
+ * catalog. It is temporary solution (although it is working). Storage manager
+ * based solution will be better, but it is not necessary for this PoC.
+ *
+ * removes uncommitted or dropped schema variables, so event can be ignored.
+ */
+static void
+recheck_schema_variables(XactEvent event, void *arg)
+{
+ HASH_SEQ_STATUS status;
+ SchemaVar var;
+
+ if (cache_is_valid || schemavarhashtab == NULL || !IsTransactionState())
+ return;
+
+ hash_seq_init(&status, schemavarhashtab);
+
+ while ((var = (SchemaVar) hash_seq_search(&status)) != NULL)
+ {
+ HeapTuple tp = InvalidOid;
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(var->varid));
+ if (!HeapTupleIsValid(tp))
+ {
+ elog(DEBUG1, "variable %d is removed from cache", var->varid);
+
+ if (var->freeval)
+ {
+ pfree(DatumGetPointer(var->value));
+ var->freeval = false;
+ }
+
+ if (hash_search(schemavarhashtab,
+ (void *) &var->varid,
+ HASH_REMOVE,
+ NULL) == NULL)
+ elog(ERROR, "hash table corrupted");
+ }
+ else
+ ReleaseSysCache(tp);
+ }
+ cache_is_valid = true;
+}
+
+/*
+ * DefineSessionVariable
+ * Creates a new variable related relation
+ */
+ObjectAddress
+DefineSchemaVariable(ParseState *pstate, CreateSchemaVarStmt *var)
+{
+ CreateStmt *stmt = makeNode(CreateStmt);
+ Oid typoid;
+ Oid varoid;
+ ObjectAddress address;
+
+ /*
+ * If if_not_exists was given and a relation with the same name already
+ * exists, bail out. (Note: we needn't check this when not if_not_exists,
+ * because DefineRelation will complain anyway.)
+ */
+ if (var->if_not_exists)
+ {
+ RangeVarGetAndCheckCreationNamespace(var->variable, NoLock, &varoid);
+ if (OidIsValid(varoid))
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("variable \"%s\" already exists, skipping",
+ var->variable->relname)));
+ return InvalidObjectAddress;
+ }
+ }
+
+ typoid = LookupTypeNameOid(pstate, var->typeName, false);
+
+ /*
+ * Don't allow composite types and arrays. The left expression of
+ * LET statement is simple in this moment (don't allow record field
+ * or array field specification). Without this support we should
+ * not to support non scalars ever.
+ */
+ if (type_is_rowtype(typoid))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Composite types are not allowed as variable type.")));
+
+ if (get_base_element_type(typoid) != InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Schema variables cannot be a array.")));
+
+ if (get_typtype(typoid) == TYPTYPE_PSEUDO)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("variable cannot be %s",
+ format_type_be(varoid))));
+
+ stmt->tableElts = NIL;
+ stmt->relation = var->variable;
+ stmt->inhRelations = NIL;
+ stmt->constraints = NIL;
+ stmt->options = NIL;
+ stmt->oncommit = ONCOMMIT_NOOP;
+ stmt->tablespacename = NULL;
+ stmt->if_not_exists = var->if_not_exists;
+
+ /*
+ * Use reloftype attribute. This attribute should be composite type for
+ * tables, but there are no reason to apply this rule for variables. Can
+ * be changed later with composite type support. In this moment I don't
+ * play with it, because I would not allow queries like:
+ * SELECT schemavar FROM schemavar, because there is semantic colission
+ * with SELECT schemavar. Users expects composite value (one attribute)
+ * from first query, but scalar from second query. This schisma can be
+ * solved by disallowing SELECT . FROM schemavar for scalar variables.
+ *
+ * On second hand - without additional fields, just with reloftype is
+ * not possible to store typmod. So all variables can be typmod less.
+ * Is not possible to store default expressions. So final design should
+ * be based on aux composite types for scalar variables.
+ *
+ * Theoretically, there can be used a reltype and reloftype together.
+ * reloftype will be scalar, and reltype will be composite one field
+ * row type. When reloftype = reltype, then schema variable is based
+ * on composite type, else schema variable is of scalar type.
+ */
+ stmt->ofTypename = var->typeName;
+
+ address = DefineRelation(stmt, RELKIND_VARIABLE, InvalidOid, NULL, NULL);
+ Assert(address.objectId != InvalidOid);
+
+ return address;
+}
+
+/*
+ * Implementation of schemavar cache. It is question if it should be in this place, or
+ * it should be storage related or cache related place? But for this moment (PoC) it
+ * can be here. Cache is implemented as hash table with own memory context.
+ */
+
+/*
+ * Create the hash table for storing schema variables
+ */
+static void
+create_schemavar_hashtable(void)
+{
+ HASHCTL ctl;
+
+ /* set callbacks */
+ if (first_time)
+ {
+
+ CacheRegisterSyscacheCallback(RELOID,
+ InvalidateSchemaVarCacheCallback,
+ (Datum) 0);
+ RegisterXactCallback(recheck_schema_variables, NULL);
+
+ first_time = false;
+ }
+
+ /* needs own long life memory context */
+ if (SchemaVarMemoryContext == NULL)
+ {
+ SchemaVarMemoryContext = AllocSetContextCreate(TopMemoryContext,
+ "schema variables",
+ ALLOCSET_START_SMALL_SIZES);
+ }
+
+ memset(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(SchemaVarData);
+ ctl.hcxt = SchemaVarMemoryContext;
+
+ schemavarhashtab = hash_create("Schema variables", 64, &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+ cache_is_valid = true;
+}
+
+/*
+ * Fast drop complete content of schema variables
+ */
+void
+ResetSchemaVariablesCache(void)
+{
+ if (schemavarhashtab)
+ {
+ hash_destroy(schemavarhashtab);
+ schemavarhashtab = NULL;
+ }
+
+ if (SchemaVarMemoryContext != NULL)
+ {
+ MemoryContextReset(SchemaVarMemoryContext);
+ }
+}
+
+/*
+ * Copy datum value to schema variables cache place
+ */
+static void
+SetValue(SchemaVar var,
+ Datum value, bool isNull,
+ Oid typid, int32 typmod)
+{
+ /* release previously stored value */
+ if (var->freeval)
+ {
+ pfree(DatumGetPointer(var->value));
+ var->freeval = false;
+ }
+
+ if (!isNull)
+ {
+ MemoryContext oldcxt;
+
+ /*
+ * cast the value if conversion is necessary.
+ * Expecting: current context is short context.
+ *
+ * QUESTION: how much should be this cast tolerant/strict?
+ */
+ if (var->typid != typid || var->typmod != typmod)
+ {
+ value = datumCast(value,
+ var->typid, var->typmod,
+ typid, typmod);
+ }
+
+ var->isnull = false;
+
+ oldcxt = MemoryContextSwitchTo(SchemaVarMemoryContext);
+
+ var->value = datumCopy(value, var->typbyval, var->typlen);
+ if (var->value != value)
+ var->freeval = true;
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+ else
+ {
+ var->value = (Datum) 0;
+ var->isnull = true;
+ }
+}
+
+/*
+ * Access functions to schema variables.
+ */
+void
+SetSchemaVariable(Oid varid, Datum value, bool isNull,
+ Oid typid, int32 typmod,
+ int16 typlen, bool typbyval)
+{
+ SchemaVar var;
+ bool found;
+
+ if (schemavarhashtab == NULL)
+ {
+ /* don't init hashtable for NULL values */
+ if (isNull)
+ return;
+
+ create_schemavar_hashtable();
+ }
+
+ var = (SchemaVar) hash_search(schemavarhashtab, &varid, HASH_ENTER, &found);
+ if (!found)
+ {
+ HeapTuple tp;
+ Form_pg_class vartup;
+
+ var->value = (Datum) 0;
+ var->isnull = true;
+ var->freeval = false;
+
+ /* now, type info for schema variable is collected */
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(varid));
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup faild for variable %u", varid);
+
+ vartup = (Form_pg_class) GETSTRUCT(tp);
+ var->typid = vartup->reloftype;
+
+ /* typmod is not saved */
+ var->typmod = -1;
+
+ ReleaseSysCache(tp);
+
+ get_typlenbyval(var->typid, &var->typlen, &var->typbyval);
+ }
+
+ SetValue(var, value, isNull, typid, typmod);
+}
+
+/*
+ * Returns variable name
+ */
+char *
+get_schemavar_name(Oid varid)
+{
+ HeapTuple relTup;
+ Form_pg_class relForm;
+ char *nspname;
+ char *relname;
+
+ relTup = SearchSysCache1(RELOID,
+ ObjectIdGetDatum(varid));
+ if (!HeapTupleIsValid(relTup))
+ elog(ERROR, "cache lookup failed for schema variable %u", varid);
+ relForm = (Form_pg_class) GETSTRUCT(relTup);
+
+ /* Qualify the name if not visible in search path */
+ if (RelationIsVisible(varid))
+ nspname = NULL;
+ else
+ nspname = get_namespace_name(relForm->relnamespace);
+
+ relname = quote_qualified_identifier(nspname, NameStr(relForm->relname));
+
+ ReleaseSysCache(relTup);
+
+ return relname;
+}
+
+/*
+ * Securized versions SetSchemaVariable
+ */
+void
+SetSchemaVariableSecure(Oid varid, Datum value, bool isNull,
+ Oid typid, int32 typmod,
+ int16 typlen, bool typbyval)
+{
+ AclResult aclresult;
+
+ /* Check permissions */
+ aclresult = pg_class_aclcheck(varid, GetUserId(), ACL_UPDATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_VARIABLE, get_schemavar_name(varid));
+
+ SetSchemaVariable(varid, value, isNull, typid, typmod, typlen, typbyval);
+}
+
+/*
+ * Cast datum
+ */
+static Datum
+datumCast(Datum value,
+ Oid target_typid, int target_typmod,
+ Oid source_typid, int source_typmod)
+{
+ CoercionPathType cpathtype;
+ Oid cfuncid;
+ Datum result = (Datum) 0;
+ bool is_binary_cast = false;
+
+ if (target_typid != source_typid)
+ {
+ cpathtype = find_coercion_pathway(target_typid, source_typid,
+ COERCION_EXPLICIT,
+ &cfuncid);
+
+ if (cpathtype == COERCION_PATH_NONE)
+ elog(ERROR, "could not find cast from %s to %s",
+ format_type_be(source_typid),
+ format_type_be(target_typid));
+
+ if (cpathtype == COERCION_PATH_RELABELTYPE)
+ {
+ result = value;
+ is_binary_cast = true;
+ }
+ else if (cpathtype == COERCION_PATH_COERCEVIAIO)
+ {
+ Oid outfunc;
+ Oid infunc;
+ Oid ioparam;
+ bool isVarlena;
+ char *str;
+
+ getTypeOutputInfo(source_typid, &outfunc, &isVarlena);
+ str = OidOutputFunctionCall(outfunc, value);
+
+ getTypeInputInfo(target_typid, &infunc, &ioparam);
+ result = OidInputFunctionCall(infunc, str, ioparam, -1);
+ }
+ else if (cpathtype == COERCION_PATH_FUNC)
+ {
+ result = OidFunctionCall3(cfuncid,
+ value,
+ Int32GetDatum(target_typmod),
+ BoolGetDatum(false));
+ }
+ }
+ else
+ {
+ result = value;
+ is_binary_cast = true;
+ }
+
+ if (target_typmod < 1 || (target_typmod == source_typmod && is_binary_cast))
+ return result;
+
+ cpathtype = find_typmod_coercion_function(target_typid, &cfuncid);
+ if (cpathtype == COERCION_PATH_FUNC)
+ {
+ result = OidFunctionCall3(cfuncid,
+ result,
+ Int32GetDatum(target_typmod),
+ BoolGetDatum(false));
+ }
+
+ return result;
+}
+
+Datum
+GetSchemaVariable(Oid varid, bool *isNull,
+ Oid typid, int32 typmod,
+ int16 typlen, bool typbyval)
+{
+ Assert(varid != InvalidOid);
+
+ if (schemavarhashtab != NULL)
+ {
+ SchemaVar var;
+ bool found;
+
+ var = (SchemaVar) hash_search(schemavarhashtab,
+ &varid, HASH_FIND, &found);
+
+ if (found && !var->isnull)
+ {
+ Datum result;
+
+ result = datumCast(var->value, typid, typmod,
+ var->typid, var->typmod);
+ *isNull = false;
+
+ if (result != var->value)
+ return result;
+ else
+ return datumCopy(result, typbyval, typlen);
+ }
+ }
+
+ /*
+ * This implementation is simple, because default expressions
+ * are not supported. With support of default expression, there
+ * should be insert schema variable into cache. Not supported yet,
+ * so do just simply work.
+ */
+ *isNull = true;
+ return (Datum) 0;
+}
+
+/*
+ * Securized version of GetSchemaVariable
+ */
+Datum
+GetSchemaVariableSecure(Oid varid, bool *isNull,
+ Oid typid, int32 typmod,
+ int16 typlen, bool typbyval)
+{
+ AclResult aclresult;
+
+ /* Check permissions */
+ aclresult = pg_class_aclcheck(varid, GetUserId(), ACL_SELECT);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_VARIABLE, get_schemavar_name(varid));
+
+ return GetSchemaVariable(varid, isNull, typid, typmod, typlen, typbyval);
+}
+
+/*
+ * V1 function API
+ *
+ * void set_schema_variable(var regclass, value anyelement);
+ * anyelement get_schema_variable(var regclass, expected_type anyelement)
+ *
+ */
+Datum
+set_schema_variable(PG_FUNCTION_ARGS)
+{
+ Oid varid;
+ Datum value;
+ bool isNull;
+ Oid typid;
+ int16 typlen;
+ bool typbyval;
+
+ if (PG_ARGISNULL(0))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed for variable identity")));
+
+ varid = PG_GETARG_OID(0);
+
+ if (!PG_ARGISNULL(1))
+ {
+ value = PG_GETARG_DATUM(1);
+ isNull = false;
+ }
+ else
+ {
+ value = (Datum) 0;
+ isNull = true;
+ }
+
+ typid = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ if (typid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not determine input data type")));
+
+ get_typlenbyval(typid, &typlen, &typbyval);
+ SetSchemaVariableSecure(varid, value, isNull, typid, -1, typlen, typbyval);
+
+ PG_RETURN_VOID();
+}
+
+Datum
+get_schema_variable(PG_FUNCTION_ARGS)
+{
+ Oid varid;
+ Oid typid;
+ int16 typlen;
+ bool typbyval;
+ bool isNull;
+ Datum result;
+
+ if (PG_ARGISNULL(0))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed for variable identity")));
+
+ varid = PG_GETARG_OID(0);
+
+ typid = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ if (typid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not determine input data type")));
+
+ get_typlenbyval(typid, &typlen, &typbyval);
+ result = GetSchemaVariableSecure(varid, &isNull, typid, -1, typlen, typbyval);
+
+ if (isNull)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_DATUM(result);
+}
+
+/*
+ * Results:
+ *=
+
+1. The schema variables are similar to temporary tables - but the data are not saved
+ in 8KB blocks, so new storage for Pg storage manager should be created.
+
+2. We should to work with typmod, so pg_attribute entry should be created anytime.
+
+3. A risk of collisions of variable and table name will be reduced, when variables
+ and tables cannot to have same name.
+
+4. If schema variables are pg_class based, then some current syntax has sense
+
+ INSERT INTO schema.variable SELECT xxx
+ maybe (but it is not consistent with PostgreSQL SQL, but consistent with PLpgSQL):
+ SELECT * INTO schema.variable FROM xxx
+
+5. LET cmd can be implemented as CMD (like INSERT, UPDATE, DELETE) or Utility (like
+ CreateTableAsSelect). Prefer first option, because there can be prepared, can be
+ used together with EXPLAIN, etc.
+
+ Expected form:
+ LET foo = (SELECT id FROM boo WHERE some = 'hello');
+
+ so possibility to run EXPLAIN LET .. has enough benefit
+*/
\ No newline at end of file
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index c6eb3ebacf..53ea890517 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -33,6 +33,7 @@
#include "access/nbtree.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_type.h"
+#include "commands/schemavar.h"
#include "executor/execExpr.h"
#include "executor/nodeSubplan.h"
#include "funcapi.h"
@@ -723,6 +724,7 @@ ExecInitExprRec(Expr *node, ExprState *state,
{
Param *param = (Param *) node;
ParamListInfo params;
+ AclResult aclresult;
switch (param->paramkind)
{
@@ -732,6 +734,23 @@ ExecInitExprRec(Expr *node, ExprState *state,
scratch.d.param.paramtype = param->paramtype;
ExprEvalPushStep(state, &scratch);
break;
+ case PARAM_SCHEMA_VARIABLE:
+ /* Check permission to read schema variable */
+ aclresult = pg_class_aclcheck(param->paramid, GetUserId(), ACL_SELECT);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_VARIABLE, get_schemavar_name(param->paramid));
+
+ scratch.opcode = EEOP_PARAM_SCHEMA_VARIABLE;
+ scratch.d.param.paramid = param->paramid;
+ scratch.d.param.paramtype = param->paramtype;
+ scratch.d.param.paramtypmod = param->paramtypmod;
+
+ get_typlenbyval(param->paramtype,
+ &scratch.d.param.paramtyplen,
+ &scratch.d.param.paramtypbyval);
+
+ ExprEvalPushStep(state, &scratch);
+ break;
case PARAM_EXTERN:
/*
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index f646fd9c51..7a3b283039 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -59,6 +59,7 @@
#include "access/tuptoaster.h"
#include "catalog/pg_type.h"
#include "commands/sequence.h"
+#include "commands/schemavar.h"
#include "executor/execExpr.h"
#include "executor/nodeSubplan.h"
#include "funcapi.h"
@@ -350,6 +351,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_PARAM_EXEC,
&&CASE_EEOP_PARAM_EXTERN,
&&CASE_EEOP_PARAM_CALLBACK,
+ &&CASE_EEOP_PARAM_SCHEMA_VARIABLE,
&&CASE_EEOP_CASE_TESTVAL,
&&CASE_EEOP_MAKE_READONLY,
&&CASE_EEOP_IOCOERCE,
@@ -1031,6 +1033,23 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_PARAM_SCHEMA_VARIABLE)
+ {
+ Datum d;
+ bool isnull;
+
+ d = GetSchemaVariable(op->d.param.paramid, &isnull,
+ op->d.param.paramtype,
+ -1,
+ op->d.param.paramtyplen,
+ op->d.param.paramtypbyval);
+
+ *op->resvalue = d;
+ *op->resnull = isnull;
+
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_PARAM_CALLBACK)
{
/* allow an extension module to supply a PARAM_EXTERN value */
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 410921cc40..a1ae732ae5 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -210,6 +210,7 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
switch (queryDesc->operation)
{
case CMD_SELECT:
+ case CMD_LET:
/*
* SELECT FOR [KEY] UPDATE/SHARE and modifying CTEs need to mark
@@ -1119,6 +1120,36 @@ CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
errmsg("cannot change TOAST relation \"%s\"",
RelationGetRelationName(resultRel))));
break;
+ case RELKIND_VARIABLE:
+
+ /* Only LET statement is allowed */
+ if (operation != CMD_LET)
+ {
+ switch (operation)
+ {
+ case CMD_INSERT:
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot insert into variable \"%s\"",
+ RelationGetRelationName(resultRel))));
+ break;
+ case CMD_UPDATE:
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot update variable \"%s\"",
+ RelationGetRelationName(resultRel))));
+ break;
+ case CMD_DELETE:
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot delete from variable \"%s\"",
+ RelationGetRelationName(resultRel))));
+ default:
+ elog(ERROR, "unrecognized CmdType: %d", (int) operation);
+ break;
+ }
+ }
+ break;
case RELKIND_VIEW:
/*
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 2a8ecbd830..f8e478aa42 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -39,6 +39,7 @@
#include "access/htup_details.h"
#include "access/xact.h"
+#include "commands/schemavar.h"
#include "commands/trigger.h"
#include "executor/execPartition.h"
#include "executor/executor.h"
@@ -68,6 +69,7 @@ static void ExecSetupChildParentMapForSubplan(ModifyTableState *mtstate);
static TupleConversionMap *tupconv_map_for_subplan(ModifyTableState *node,
int whichplan);
+
/*
* Verify that the tuples to be produced by INSERT or UPDATE match the
* target relation's rowtype
@@ -1568,6 +1570,81 @@ ExecOnConflictUpdate(ModifyTableState *mtstate,
}
+
+
+
+/* ----------------------------------------------------------------
+ * ExecLet
+ *
+ * For LET, we have to update target variable,
+ * Returns NULL, there are not RETURNING clause.
+ * ----------------------------------------------------------------
+ */
+static TupleTableSlot *
+ExecLet(ModifyTableState *mtstate,
+ TupleTableSlot *slot,
+ EState *estate,
+ bool canSetTag)
+{
+ HeapTuple tuple;
+ ResultRelInfo *resultRelInfo;
+ Relation resultRelationDesc;
+ TupleDesc tupdesc;
+ bool isnull = true;
+ Datum value;
+ Form_pg_attribute attr = NULL;
+ Oid varid;
+
+ if (slot != NULL && !slot->tts_isempty)
+ {
+ tuple = slot->tts_tuple;
+ tupdesc = slot->tts_tupleDescriptor;
+
+ Assert(tupdesc != NULL);
+
+ /* should be checked before */
+ if (tupdesc->natts != 1)
+ elog(ERROR, "unexpected number of attributes");
+
+ attr = TupleDescAttr(tupdesc, 0);
+
+ if (!slot->tts_isnull[0])
+ {
+ isnull = false;
+ value = slot->tts_values[0];
+ }
+ }
+
+ /*
+ * Now, es_result_relation_info is empty, but can be initialized
+ * to structure of used schema variable.
+ */
+ resultRelInfo = estate->es_result_relation_info;
+ resultRelationDesc = resultRelInfo->ri_RelationDesc;
+ varid = resultRelationDesc->rd_id;
+
+ if (!isnull)
+ {
+ /* expecting so variable and expression are equal */
+ SetSchemaVariable(varid, value, isnull,
+ attr->atttypid, -1,
+ attr->attlen, attr->attbyval);
+ }
+ else
+ {
+ SetSchemaVariable(varid, (Datum) 0, true,
+ InvalidOid, -1, -1, false);
+ }
+
+ if (canSetTag)
+ {
+ Assert(estate->es_processed == 0);
+ (estate->es_processed)++;
+ }
+
+ return NULL;
+}
+
/*
* Process BEFORE EACH STATEMENT triggers
*/
@@ -1598,6 +1675,9 @@ fireBSTriggers(ModifyTableState *node)
case CMD_DELETE:
ExecBSDeleteTriggers(node->ps.state, resultRelInfo);
break;
+ case CMD_LET:
+ /* there are no trigger */
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -1652,6 +1732,9 @@ fireASTriggers(ModifyTableState *node)
ExecASDeleteTriggers(node->ps.state, resultRelInfo,
node->mt_transition_capture);
break;
+ case CMD_LET:
+ /* variables has not triggers */
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -2056,6 +2139,9 @@ ExecModifyTable(PlanState *pstate)
&node->mt_epqstate, estate,
NULL, true, node->canSetTag);
break;
+ case CMD_LET:
+ slot = ExecLet(node, slot, estate, node->canSetTag);
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -2562,6 +2648,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
break;
case CMD_UPDATE:
case CMD_DELETE:
+ case CMD_LET:
junk_filter_needed = true;
break;
default:
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 9fc4431b80..310bc3f2c7 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -2404,6 +2404,9 @@ _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, uint64 tcount)
else
res = SPI_OK_UPDATE;
break;
+ case CMD_LET:
+ res = SPI_OK_UTILITY;
+ break;
default:
return SPI_ERROR_OPUNKNOWN;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index bafe0d1071..bf250aafbb 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3056,6 +3056,17 @@ _copySelectStmt(const SelectStmt *from)
return newnode;
}
+static LetStmt *
+_copyLetStmt(const LetStmt *from)
+{
+ LetStmt *newnode = makeNode(LetStmt);
+
+ COPY_NODE_FIELD(variable);
+ COPY_NODE_FIELD(selectStmt);
+
+ return newnode;
+}
+
static SetOperationStmt *
_copySetOperationStmt(const SetOperationStmt *from)
{
@@ -5091,6 +5102,9 @@ copyObjectImpl(const void *from)
case T_SelectStmt:
retval = _copySelectStmt(from);
break;
+ case T_LetStmt:
+ retval = _copyLetStmt(from);
+ break;
case T_SetOperationStmt:
retval = _copySetOperationStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 02ca7d588c..709a686134 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1068,6 +1068,15 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
}
static bool
+_equalLetStmt(const LetStmt *a, const LetStmt *b)
+{
+ COMPARE_NODE_FIELD(variable);
+ COMPARE_NODE_FIELD(selectStmt);
+
+ return true;
+}
+
+static bool
_equalSetOperationStmt(const SetOperationStmt *a, const SetOperationStmt *b)
{
COMPARE_SCALAR_FIELD(op);
@@ -3228,6 +3237,9 @@ equal(const void *a, const void *b)
case T_SelectStmt:
retval = _equalSelectStmt(a, b);
break;
+ case T_LetStmt:
+ retval = _equalLetStmt(a, b);
+ break;
case T_SetOperationStmt:
retval = _equalSetOperationStmt(a, b);
break;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 6c76c41ebe..8d24818c9f 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3444,6 +3444,16 @@ raw_expression_tree_walker(Node *node,
return true;
}
break;
+ case T_LetStmt:
+ {
+ LetStmt *stmt = (LetStmt *) node;
+
+ if (walker(stmt->variable, context))
+ return true;
+ if (walker(stmt->selectStmt, context))
+ return true;
+ }
+ break;
case T_A_Expr:
{
A_Expr *expr = (A_Expr *) node;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 89f27ce0eb..f4d8756487 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1251,12 +1251,15 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
* PARAM_EXEC Params listed in safe_param_ids, meaning they could be
* either generated within the worker or can be computed in master and
* then their value can be passed to the worker.
+ * PARAM_SCHEMA_VARIABLE params are newer changed by workers, so they can be
+ * safe.
*/
else if (IsA(node, Param))
{
Param *param = (Param *) node;
- if (param->paramkind == PARAM_EXTERN)
+ if (param->paramkind == PARAM_EXTERN ||
+ param->paramkind == PARAM_SCHEMA_VARIABLE)
return false;
if (param->paramkind != PARAM_EXEC ||
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index e7b2bc7e73..f22eab422e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -42,6 +42,7 @@
#include "parser/parse_target.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
+#include "utils/builtins.h"
#include "utils/rel.h"
@@ -54,6 +55,7 @@ static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt);
static List *transformInsertRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos,
bool strip_indirection);
+static Query *transformLetStmt(ParseState *pstate, LetStmt *stmt);
static OnConflictExpr *transformOnConflictClause(ParseState *pstate,
OnConflictClause *onConflictClause);
static int count_rowexpr_columns(ParseState *pstate, Node *expr);
@@ -263,6 +265,7 @@ transformStmt(ParseState *pstate, Node *parseTree)
case T_InsertStmt:
case T_UpdateStmt:
case T_DeleteStmt:
+ case T_LetStmt:
(void) test_raw_expression_coverage(parseTree, NULL);
break;
default:
@@ -300,6 +303,10 @@ transformStmt(ParseState *pstate, Node *parseTree)
}
break;
+ case T_LetStmt:
+ result = transformLetStmt(pstate, (LetStmt *) parseTree);
+ break;
+
/*
* Special cases
*/
@@ -358,6 +365,7 @@ analyze_requires_snapshot(RawStmt *parseTree)
case T_DeleteStmt:
case T_UpdateStmt:
case T_SelectStmt:
+ case T_LetStmt:
result = true;
break;
@@ -1533,6 +1541,207 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
}
/*
+ * transformLetStmt -
+ * transform an Let Statement
+ */
+static Query *
+transformLetStmt(ParseState *pstate, LetStmt *stmt)
+{
+ Query *qry = makeNode(Query);
+ List *exprList = NIL;
+ List *exprListCoer = NIL;
+ List *sub_rtable;
+ List *sub_namespace;
+ RangeTblEntry *rte;
+ RangeTblRef *rtr;
+ ListCell *lc;
+ AclMode targetPerms;
+ ParseState *sub_pstate;
+ Query *selectQuery;
+ int i = 0;
+
+ Relation rd;
+ Oid vartypid = InvalidOid;
+
+ /* There can't be any outer WITH to worry about */
+ Assert(pstate->p_ctenamespace == NIL);
+
+ qry->commandType = CMD_LET;
+ pstate->p_is_let = true;
+
+ /*
+ * If a non-nil rangetable/namespace was passed in, and we are doing
+ * INSERT/SELECT, arrange to pass the rangetable/namespace down to the
+ * SELECT. This can only happen if we are inside a CREATE RULE, and in
+ * that case we want the rule's OLD and NEW rtable entries to appear as
+ * part of the SELECT's rtable, not as outer references for it. (Kluge!)
+ * The SELECT's joinlist is not affected however. We must do this before
+ * adding the target table to the INSERT's rtable.
+ */
+ sub_rtable = pstate->p_rtable;
+ pstate->p_rtable = NIL;
+ sub_namespace = pstate->p_namespace;
+ pstate->p_namespace = NIL;
+
+ targetPerms = ACL_UPDATE;
+ qry->resultRelation = setTargetTable(pstate, stmt->variable,
+ false, false, targetPerms);
+
+ rd = pstate->p_target_relation;
+ vartypid = rd->rd_rel->reloftype;
+
+ /*
+ * We make the sub-pstate a child of the outer pstate so that it can
+ * see any Param definitions supplied from above. Since the outer
+ * pstate's rtable and namespace are presently empty, there are no
+ * side-effects of exposing names the sub-SELECT shouldn't be able to
+ * see.
+ */
+ sub_pstate = make_parsestate(pstate);
+
+ /*
+ * Process the source SELECT.
+ *
+ * It is important that this be handled just like a standalone SELECT;
+ * otherwise the behavior of SELECT within INSERT might be different
+ * from a stand-alone SELECT. (Indeed, Postgres up through 6.5 had
+ * bugs of just that nature...)
+ *
+ * The sole exception is that we prevent resolving unknown-type
+ * outputs as TEXT. This does not change the semantics since if the
+ * column type matters semantically, it would have been resolved to
+ * something else anyway. Doing this lets us resolve such outputs as
+ * the target column's type, which we handle below.
+ */
+ sub_pstate->p_rtable = sub_rtable;
+ sub_pstate->p_joinexprs = NIL; /* sub_rtable has no joins */
+ sub_pstate->p_namespace = sub_namespace;
+ sub_pstate->p_resolve_unknowns = false;
+
+ selectQuery = transformStmt(sub_pstate, stmt->selectStmt);
+
+ free_parsestate(sub_pstate);
+
+ /* The grammar should have produced a SELECT */
+ if (!IsA(selectQuery, Query) ||
+ selectQuery->commandType != CMD_SELECT)
+ elog(ERROR, "unexpected non-SELECT command in LET ... SELECT");
+
+ /*
+ * Make the source be a subquery in the LET's rangetable, and add
+ * it to the LET's joinlist.
+ */
+ rte = addRangeTableEntryForSubquery(pstate,
+ selectQuery,
+ makeAlias("*SELECT*", NIL),
+ false,
+ false);
+ rtr = makeNode(RangeTblRef);
+ /* assume new rte is at end */
+ rtr->rtindex = list_length(pstate->p_rtable);
+ Assert(rte == rt_fetch(rtr->rtindex, pstate->p_rtable));
+ pstate->p_joinlist = lappend(pstate->p_joinlist, rtr);
+
+ /*----------
+ * Generate an expression list for the LET that selects all the
+ * non-resjunk columns from the subquery. (LET's tlist must be
+ * separate from the subquery's tlist because we may add datatype
+ * coercions, etc.)
+ *----------
+ */
+ exprList = NIL;
+ foreach(lc, selectQuery->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Expr *expr;
+
+ if (tle->resjunk)
+ continue;
+ if (tle->expr &&
+ (IsA(tle->expr, Const) ||IsA(tle->expr, Param)) &&
+ exprType((Node *) tle->expr) == UNKNOWNOID)
+ expr = tle->expr;
+ else
+ {
+ Var *var = makeVarFromTargetEntry(rtr->rtindex, tle);
+
+ var->location = exprLocation((Node *) tle->expr);
+ expr = (Expr *) var;
+ }
+ exprList = lappend(exprList, expr);
+ }
+
+ /*
+ * Because supports only scalar variables, we can only simple
+ * transformations and checks here.
+ */
+ if (list_length(exprList) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("expression is not scalar value"),
+ parser_errposition(pstate,
+ exprLocation((Node *) exprList))));
+
+ exprListCoer = NIL;
+ foreach(lc, exprList)
+ {
+ Node *orig_expr = (Node*) lfirst(lc);
+ Oid exprtypid = exprType((Node *) orig_expr);
+ Expr *expr;
+
+ expr = (Expr *)
+ coerce_to_target_type(pstate,
+ orig_expr, exprtypid,
+ vartypid, -1,
+ COERCION_ASSIGNMENT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+
+ if (expr == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("variable \"%s\" is of type %s"
+ " but expression is of type %s",
+ RelationGetRelationName(rd),
+ format_type_be(vartypid),
+ format_type_be(exprtypid)),
+ errhint("You will need to rewrite or cast the expression."),
+ parser_errposition(pstate, exprLocation((Node *) orig_expr))));
+
+ exprListCoer = lappend(exprListCoer, expr);
+ }
+
+ /*
+ * Generate query's target list using the computed list of expressions.
+ * Also, mark all the target columns as needing insert permissions.
+ */
+ rte = pstate->p_target_rangetblentry;
+ qry->targetList = NIL;
+ foreach(lc, exprList)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+ TargetEntry *tle;
+
+ tle = makeTargetEntry(expr,
+ i + 1,
+ FigureColname((Node *)expr),
+ false);
+ qry->targetList = lappend(qry->targetList, tle);
+ }
+
+ /* done building the range table and jointree */
+ qry->rtable = pstate->p_rtable;
+ qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
+
+ qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
+ qry->hasSubLinks = pstate->p_hasSubLinks;
+
+ assign_query_collations(pstate, qry);
+
+ return qry;
+}
+
+/*
* transformSetOperationStmt -
* transforms a set-operations tree
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 5329432f25..d2a264d1e1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -257,8 +257,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt
CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt
- CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt
- CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
+ CreateSchemaStmt CreateSchemaVarStmt CreateSeqStmt CreateStmt CreateStatsStmt
+ CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
CreateAssertStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt
CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
@@ -268,7 +268,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
DropTransformStmt
DropUserMappingStmt ExplainStmt FetchStmt
GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt
- ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt
+ LetStmt ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt
CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
@@ -646,7 +646,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
KEY
LABEL LANGUAGE LARGE_P LAST_P LATERAL_P
- LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
+ LEADING LEAKPROOF LEAST LEFT LET LEVEL LIKE LIMIT LISTEN LOAD LOCAL
LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED
MAPPING MATCH MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE
@@ -682,8 +682,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED
UNTIL UPDATE USER USING
- VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VOLATILE
+ VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIABLE VARIABLES
+ VARIADIC VARYING VERBOSE VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -873,6 +873,7 @@ stmt :
| CreatePLangStmt
| CreateSchemaStmt
| CreateSeqStmt
+ | CreateSchemaVarStmt
| CreateStmt
| CreateSubscriptionStmt
| CreateStatsStmt
@@ -914,6 +915,7 @@ stmt :
| ListenStmt
| RefreshMatViewStmt
| LoadStmt
+ | LetStmt
| LockStmt
| NotifyStmt
| PrepareStmt
@@ -1374,6 +1376,7 @@ schema_stmt:
CreateStmt
| IndexStmt
| CreateSeqStmt
+ | CreateSchemaVarStmt
| CreateTrigStmt
| GrantStmt
| ViewStmt
@@ -1802,7 +1805,12 @@ DiscardStmt:
n->target = DISCARD_SEQUENCES;
$$ = (Node *) n;
}
-
+ | DISCARD VARIABLES
+ {
+ DiscardStmt *n = makeNode(DiscardStmt);
+ n->target = DISCARD_VARIABLES;
+ $$ = (Node *) n;
+ }
;
@@ -4269,6 +4277,34 @@ NumericOnly_list: NumericOnly { $$ = list_make1($1); }
/*****************************************************************************
*
+ * QUERY :
+ * CREATE VARIABLE seqname [AS] type
+ *
+ *****************************************************************************/
+
+CreateSchemaVarStmt:
+ CREATE OptTemp VARIABLE qualified_name opt_as Typename
+ {
+ CreateSchemaVarStmt *n = makeNode(CreateSchemaVarStmt);
+ $4->relpersistence = $2;
+ n->variable = $4;
+ n->typeName = $6;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
+ | CREATE OptTemp VARIABLE IF_P NOT EXISTS qualified_name opt_as Typename
+ {
+ CreateSchemaVarStmt *n = makeNode(CreateSchemaVarStmt);
+ $7->relpersistence = $2;
+ n->variable = $7;
+ n->typeName = $9;
+ n->if_not_exists = true;
+ $$ = (Node *)n;
+ }
+ ;
+
+/*****************************************************************************
+ *
* QUERIES :
* CREATE [OR REPLACE] [TRUSTED] [PROCEDURAL] LANGUAGE ...
* DROP [PROCEDURAL] LANGUAGE ...
@@ -6315,6 +6351,7 @@ drop_type_any_name:
| TEXT_P SEARCH DICTIONARY { $$ = OBJECT_TSDICTIONARY; }
| TEXT_P SEARCH TEMPLATE { $$ = OBJECT_TSTEMPLATE; }
| TEXT_P SEARCH CONFIGURATION { $$ = OBJECT_TSCONFIGURATION; }
+ | VARIABLE { $$ = OBJECT_VARIABLE; }
;
/* object types taking name_list */
@@ -6584,6 +6621,7 @@ comment_type_any_name:
| TEXT_P SEARCH DICTIONARY { $$ = OBJECT_TSDICTIONARY; }
| TEXT_P SEARCH PARSER { $$ = OBJECT_TSPARSER; }
| TEXT_P SEARCH TEMPLATE { $$ = OBJECT_TSTEMPLATE; }
+ | VARIABLE { $$ = OBJECT_VARIABLE; }
;
/* object types taking name */
@@ -6722,6 +6760,7 @@ security_label_type_any_name:
| TABLE { $$ = OBJECT_TABLE; }
| VIEW { $$ = OBJECT_VIEW; }
| MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; }
+ | VARIABLE { $$ = OBJECT_VARIABLE; }
;
/* object types taking name */
@@ -7047,6 +7086,14 @@ privilege_target:
n->objs = $2;
$$ = n;
}
+ | VARIABLE qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
+ n->objtype = OBJECT_VARIABLE;
+ n->objs = $2;
+ $$ = n;
+ }
| FOREIGN DATA_P WRAPPER name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
@@ -7159,6 +7206,14 @@ privilege_target:
n->objs = $5;
$$ = n;
}
+ | ALL VARIABLES IN_P SCHEMA name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_ALL_IN_SCHEMA;
+ n->objtype = OBJECT_VARIABLE;
+ n->objs = $5;
+ $$ = n;
+ }
| ALL FUNCTIONS IN_P SCHEMA name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
@@ -7341,6 +7396,7 @@ defacl_privilege_target:
| FUNCTIONS { $$ = OBJECT_FUNCTION; }
| ROUTINES { $$ = OBJECT_FUNCTION; }
| SEQUENCES { $$ = OBJECT_SEQUENCE; }
+ | VARIABLES { $$ = OBJECT_VARIABLE; }
| TYPES_P { $$ = OBJECT_TYPE; }
| SCHEMAS { $$ = OBJECT_SCHEMA; }
;
@@ -10645,7 +10701,8 @@ ExplainableStmt:
| CreateAsStmt
| CreateMatViewStmt
| RefreshMatViewStmt
- | ExecuteStmt /* by default all are $$=$1 */
+ | ExecuteStmt
+ | LetStmt /* by default all are $$=$1 */
;
explain_option_list:
@@ -10702,7 +10759,8 @@ PreparableStmt:
SelectStmt
| InsertStmt
| UpdateStmt
- | DeleteStmt /* by default all are $$=$1 */
+ | DeleteStmt
+ | LetStmt /* by default all are $$=$1 */
;
/*****************************************************************************
@@ -11104,6 +11162,30 @@ opt_hold: /* EMPTY */ { $$ = 0; }
/*****************************************************************************
*
* QUERY:
+ * LET STATEMENTS
+ *
+ *****************************************************************************/
+LetStmt: LET qualified_name '=' a_expr
+ {
+ LetStmt *n = makeNode(LetStmt);
+ SelectStmt *select = makeNode(SelectStmt);
+ ResTarget *res = makeNode(ResTarget);
+
+ res->name = NULL;
+ res->indirection = NIL;
+ res->val = (Node *) $4;
+ res->location = @4;
+ select->targetList = list_make1(res);
+ n->variable = $2;
+ n->selectStmt = (Node *) select;
+
+ $$ = (Node *) n;
+ }
+ ;
+
+/*****************************************************************************
+ *
+ * QUERY:
* SELECT STATEMENTS
*
*****************************************************************************/
@@ -15056,6 +15138,7 @@ unreserved_keyword:
| LARGE_P
| LAST_P
| LEAKPROOF
+ | LET
| LEVEL
| LISTEN
| LOAD
@@ -15202,6 +15285,8 @@ unreserved_keyword:
| VALIDATE
| VALIDATOR
| VALUE_P
+ | VARIABLE
+ | VARIABLES
| VARYING
| VERSION_P
| VIEW
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index b2f5e46e3b..cbf757d059 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -509,6 +509,7 @@ static Node *
transformColumnRef(ParseState *pstate, ColumnRef *cref)
{
Node *node = NULL;
+ Node *variable = NULL;
char *nspname = NULL;
char *relname = NULL;
char *colname = NULL;
@@ -750,6 +751,70 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
}
/*
+ * Try to identify column ref as variable. Possible variants are
+ *
+ * A .. variable name
+ * A.B .. qualified variable name
+ */
+ switch (list_length(cref->fields))
+ {
+ case 1:
+ {
+ Node *field1 = (Node *) linitial(cref->fields);
+
+ if (IsA(field1, String))
+ {
+ char *varname = strVal(field1);
+
+ /* Try to identify as an unqualified column */
+ variable = toSchemaVariable(pstate,
+ NULL, varname,
+ cref->location);
+ }
+ break;
+ }
+ case 2:
+ {
+ Node *field1 = (Node *) linitial(cref->fields);
+ Node *field2 = (Node *) lsecond(cref->fields);
+
+ if (IsA(field1, String) && IsA(field2, String))
+ {
+ char *nspname = strVal(field1);
+ char *varname = strVal(field2);
+
+ /* Try to identify as an unqualified column */
+ variable = toSchemaVariable(pstate,
+ nspname, varname,
+ cref->location);
+ }
+ break;
+ }
+ default:
+
+ /*
+ * There can be another variants, more when composite variables
+ * will be supported. Currently only scalars are supported, so
+ * there are not necessary to solve other questions.
+ *
+ * do nothing
+ */
+ break;
+ }
+
+ if (variable != NULL)
+ {
+ if (node != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_AMBIGUOUS_COLUMN),
+ errmsg("column reference \"%s\" is ambiguous",
+ NameListToString(cref->fields)),
+ parser_errposition(pstate, cref->location)));
+
+ node = variable;
+ }
+
+ /*
* Now give the PostParseColumnRefHook, if any, a chance. We pass the
* translation-so-far so that it can throw an error if it wishes in the
* case that it has a conflicting interpretation of the ColumnRef. (If it
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 2625da5327..f7d9a0c939 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1150,6 +1150,7 @@ parserOpenTable(ParseState *pstate, const RangeVar *relation, int lockmode)
setup_parser_errposition_callback(&pcbstate, pstate, relation->location);
rel = heap_openrv_extended(relation, lockmode, true);
+
if (rel == NULL)
{
if (relation->schemaname)
@@ -1180,6 +1181,24 @@ parserOpenTable(ParseState *pstate, const RangeVar *relation, int lockmode)
relation->relname)));
}
}
+
+ /*
+ * RELKIND_VARIABLE can be used only in LET command.
+ * Probably this check can be done elsewhere, but here I
+ * have a used relation and parse state together first time.
+ */
+ if (rel->rd_rel->relkind == RELKIND_VARIABLE && !pstate->p_is_let)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is an schema variable",
+ RelationGetRelationName(rel))));
+
+ if (pstate->p_is_let && rel->rd_rel->relkind != RELKIND_VARIABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not an schema variable",
+ RelationGetRelationName(rel))));
+
cancel_parser_errposition_callback(&pcbstate);
return rel;
}
@@ -3360,3 +3379,42 @@ isQueryUsingTempRelation_walker(Node *node, void *context)
isQueryUsingTempRelation_walker,
context);
}
+
+/*
+ * Try to replace ColumnRef by Param related to variable
+ */
+Node *
+toSchemaVariable(ParseState *pstate, char *nspname, char *varname, int location)
+{
+ Oid varid;
+ Param *param = NULL;
+
+ varid = RangeVarGetRelid(makeRangeVar(nspname, varname, -1), NoLock, true);
+ if (OidIsValid(varid))
+ {
+ HeapTuple tp;
+ Form_pg_class vartup;
+
+ /* now, type info for schema variable is collected */
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(varid));
+ if (HeapTupleIsValid(tp))
+ {
+ vartup = (Form_pg_class) GETSTRUCT(tp);
+
+ if (vartup->relkind == RELKIND_VARIABLE)
+ {
+ param = makeNode(Param);
+ param->paramkind = PARAM_SCHEMA_VARIABLE;
+ param->paramid = varid;
+ param->paramtype = vartup->reloftype;
+ param->paramtypmod = -1;
+ param->paramcollid = get_typcollation(param->paramtype);
+ param->location = location;
+ }
+
+ ReleaseSysCache(tp);
+ }
+ }
+
+ return (Node *) param;
+}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index d415d7180f..8c352f9293 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -106,6 +106,7 @@ typedef struct
List *views; /* CREATE VIEW items */
List *indexes; /* CREATE INDEX items */
List *triggers; /* CREATE TRIGGER items */
+ List *variables; /* CREATE VARIABLE items */
List *grants; /* GRANT items */
} CreateSchemaStmtContext;
@@ -3186,6 +3187,7 @@ transformCreateSchemaStmt(CreateSchemaStmt *stmt)
cxt.views = NIL;
cxt.indexes = NIL;
cxt.triggers = NIL;
+ cxt.variables = NIL;
cxt.grants = NIL;
/*
@@ -3251,6 +3253,14 @@ transformCreateSchemaStmt(CreateSchemaStmt *stmt)
}
break;
+ case T_CreateSchemaVarStmt:
+ {
+ CreateSchemaVarStmt *elp = (CreateSchemaVarStmt *) element;
+
+ setSchemaName(cxt.schemaname, &elp->variable->schemaname);
+ cxt.variables = lappend(cxt.variables, element);
+ }
+
case T_GrantStmt:
cxt.grants = lappend(cxt.grants, element);
break;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 66253fc3d3..47a9b211d8 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3337,7 +3337,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
rt_entry_relation,
parsetree->resultRelation, NULL);
}
- else if (event == CMD_DELETE)
+ else if (event == CMD_DELETE || event == CMD_LET)
{
/* Nothing to do here */
}
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 66cc5c35c6..34ddb79a3d 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -193,6 +193,10 @@ ProcessQuery(PlannedStmt *plan,
"DELETE " UINT64_FORMAT,
queryDesc->estate->es_processed);
break;
+ case CMD_LET:
+ snprintf(completionTag, COMPLETION_TAG_BUFSIZE,
+ "LET ");
+ break;
default:
strcpy(completionTag, "???");
break;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3abe7d6155..27a21c48da 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -47,6 +47,7 @@
#include "commands/proclang.h"
#include "commands/publicationcmds.h"
#include "commands/schemacmds.h"
+#include "commands/schemavar.h"
#include "commands/seclabel.h"
#include "commands/sequence.h"
#include "commands/subscriptioncmds.h"
@@ -112,6 +113,7 @@ CommandIsReadOnly(PlannedStmt *pstmt)
case CMD_DELETE:
return false;
case CMD_UTILITY:
+ case CMD_LET:
/* For now, treat all utility commands as read/write */
return false;
default:
@@ -177,6 +179,7 @@ check_xact_readonly(Node *parsetree)
case T_CreateSchemaStmt:
case T_CreateSeqStmt:
case T_CreateStmt:
+ case T_CreateSchemaVarStmt:
case T_CreateTableAsStmt:
case T_RefreshMatViewStmt:
case T_CreateTableSpaceStmt:
@@ -1474,6 +1477,10 @@ ProcessUtilitySlow(ParseState *pstate,
address = AlterSequence(pstate, (AlterSeqStmt *) parsetree);
break;
+ case T_CreateSchemaVarStmt:
+ address = DefineSchemaVariable(pstate, (CreateSchemaVarStmt *) parsetree);
+ break;
+
case T_CreateTableAsStmt:
address = ExecCreateTableAs((CreateTableAsStmt *) parsetree,
queryString, params, queryEnv,
@@ -2095,6 +2102,10 @@ CreateCommandTag(Node *parsetree)
tag = "SELECT";
break;
+ case T_LetStmt:
+ tag = "LET";
+ break;
+
/* utility statements --- same whether raw or cooked */
case T_TransactionStmt:
{
@@ -2259,6 +2270,9 @@ CreateCommandTag(Node *parsetree)
case OBJECT_INDEX:
tag = "DROP INDEX";
break;
+ case OBJECT_VARIABLE:
+ tag = "DROP VARIABLE";
+ break;
case OBJECT_TYPE:
tag = "DROP TYPE";
break;
@@ -2513,6 +2527,10 @@ CreateCommandTag(Node *parsetree)
tag = "ALTER SEQUENCE";
break;
+ case T_CreateSchemaVarStmt:
+ tag = "CREATE VARIABLE";
+ break;
+
case T_DoStmt:
tag = "DO";
break;
@@ -2630,6 +2648,9 @@ CreateCommandTag(Node *parsetree)
case DISCARD_SEQUENCES:
tag = "DISCARD SEQUENCES";
break;
+ case DISCARD_VARIABLES:
+ tag = "DISCARD VARIABLES";
+ break;
default:
tag = "???";
}
@@ -2834,6 +2855,9 @@ CreateCommandTag(Node *parsetree)
case CMD_DELETE:
tag = "DELETE";
break;
+ case CMD_LET:
+ tag = "LET";
+ break;
case CMD_UTILITY:
tag = CreateCommandTag(stmt->utilityStmt);
break;
@@ -2952,6 +2976,10 @@ GetCommandLogLevel(Node *parsetree)
lev = LOGSTMT_ALL;
break;
+ case T_LetStmt:
+ lev = LOGSTMT_ALL;
+ break;
+
/* utility statements --- same whether raw or cooked */
case T_TransactionStmt:
lev = LOGSTMT_ALL;
@@ -3405,6 +3433,7 @@ GetCommandLogLevel(Node *parsetree)
switch (stmt->commandType)
{
case CMD_SELECT:
+ case CMD_LET:
lev = LOGSTMT_ALL;
break;
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 0cfc297b65..fcd695836a 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -808,6 +808,10 @@ acldefault(ObjectType objtype, Oid ownerId)
world_default = ACL_USAGE;
owner_default = ACL_ALL_RIGHTS_TYPE;
break;
+ case OBJECT_VARIABLE:
+ world_default = ACL_NO_RIGHTS;
+ owner_default = ACL_ALL_RIGHTS_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized objtype: %d", (int) objtype);
world_default = ACL_NO_RIGHTS; /* keep compiler quiet */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index c5f5a1ca3f..ba592be4ae 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -41,6 +41,7 @@
#include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "commands/tablespace.h"
+#include "commands/schemavar.h"
#include "common/keywords.h"
#include "executor/spi.h"
#include "funcapi.h"
@@ -379,6 +380,7 @@ static void get_update_query_targetlist_def(Query *query, List *targetList,
deparse_context *context,
RangeTblEntry *rte);
static void get_delete_query_def(Query *query, deparse_context *context);
+static void get_let_query_def(Query *query, deparse_context *context);
static void get_utility_query_def(Query *query, deparse_context *context);
static void get_basic_select_query(Query *query, deparse_context *context,
TupleDesc resultDesc);
@@ -4926,6 +4928,10 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace,
get_delete_query_def(query, &context);
break;
+ case CMD_LET:
+ get_let_query_def(query, &context);
+ break;
+
case CMD_NOTHING:
appendStringInfoString(buf, "NOTHING");
break;
@@ -6134,6 +6140,58 @@ get_insert_query_def(Query *query, deparse_context *context)
}
}
+/* ----------
+ * get_let_query_def - Parse back an LET parsetree
+ * ----------
+ */
+static void
+get_let_query_def(Query *query, deparse_context *context)
+{
+ StringInfo buf = context->buf;
+ RangeTblEntry *select_rte = NULL;
+ RangeTblEntry *rte;
+ ListCell *l;
+
+ /*
+ * If it's an INSERT ... SELECT or multi-row VALUES, there will be a
+ * single RTE for the SELECT or VALUES. Plain VALUES has neither.
+ */
+ foreach(l, query->rtable)
+ {
+ rte = (RangeTblEntry *) lfirst(l);
+
+ if (rte->rtekind == RTE_SUBQUERY)
+ {
+ if (select_rte)
+ elog(ERROR, "too many subquery RTEs in INSERT");
+ select_rte = rte;
+ }
+ }
+
+ /*
+ * Start the query with INSERT INTO relname
+ */
+ rte = rt_fetch(query->resultRelation, query->rtable);
+ Assert(rte->rtekind == RTE_RELATION);
+
+ if (PRETTY_INDENT(context))
+ {
+ context->indentLevel += PRETTYINDENT_STD;
+ appendStringInfoChar(buf, ' ');
+ }
+ appendStringInfo(buf, "LET %s ",
+ generate_relation_name(rte->relid, NIL));
+
+ appendStringInfo(buf, " = ");
+
+ if (select_rte)
+ {
+ /* Add the SELECT */
+ get_query_def(select_rte->subquery, buf, NIL, NULL,
+ context->prettyFlags, context->wrapColumn,
+ context->indentLevel);
+ }
+}
/* ----------
* get_update_query_def - Parse back an UPDATE parsetree
@@ -7208,6 +7266,13 @@ get_parameter(Param *param, deparse_context *context)
deparse_namespace *dpns;
ListCell *ancestor_cell;
+ if (param->paramkind == PARAM_SCHEMA_VARIABLE)
+ {
+ appendStringInfo(context->buf, "%s", get_schemavar_name(param->paramid));
+
+ return;
+ }
+
/*
* If it's a PARAM_EXEC parameter, try to locate the expression from which
* the parameter was computed. Note that failing to find a referent isn't
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 3560318749..ad0030c0dd 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -794,6 +794,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'i':
case 's':
case 'E':
+ case 'V':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 466a78004b..c272de2baa 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1683,6 +1683,42 @@ describeOneTableDetails(const char *schemaname,
retval = true;
goto error_return; /* not an error, just return early */
}
+ else if (tableinfo.relkind == RELKIND_VARIABLE)
+ {
+ PGresult *res = NULL;
+ printQueryOpt myopt = pset.popt;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.format_type(reloftype, NULL) AS \"%s\"\n"
+ "FROM pg_catalog.pg_class\n"
+ "WHERE oid = '%s';",
+ gettext_noop("Type"),
+ oid);
+
+ res = PSQLexec(buf.data);
+ if (!res)
+ goto error_return;
+
+ /* Did we get anything? */
+ if (PQntuples(res) == 0)
+ {
+ if (!pset.quiet)
+ psql_error("Did not find any variable with OID %s.\n", oid);
+ goto error_return;
+ }
+
+ printfPQExpBuffer(&title, _("Schema variable \"%s.%s\""),
+ schemaname, relationname);
+
+ myopt.title = title.data;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+
+ retval = true;
+ goto error_return; /* not an error, just return early */
+ }
/*
* Get column info
@@ -3365,6 +3401,7 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
* m - materialized views
* s - sequences
* E - foreign table (Note: different from 'f', the relkind value)
+ * V - schema variable
* (any order of the above is fine)
*/
bool
@@ -3376,6 +3413,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
bool showMatViews = strchr(tabtypes, 'm') != NULL;
bool showSeq = strchr(tabtypes, 's') != NULL;
bool showForeign = strchr(tabtypes, 'E') != NULL;
+ bool showVariables = strchr(tabtypes, 'V') != NULL;
PQExpBufferData buf;
PGresult *res;
@@ -3383,8 +3421,8 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
static const bool translate_columns[] = {false, false, true, false, false, false, false};
/* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */
- if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
- showTables = showViews = showMatViews = showSeq = showForeign = true;
+ if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign || showVariables))
+ showTables = showViews = showMatViews = showSeq = showForeign = showVariables = true;
initPQExpBuffer(&buf);
@@ -3405,6 +3443,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
" WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
" WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
+ " WHEN " CppAsString2(RELKIND_VARIABLE) " THEN '%s'"
" END as \"%s\",\n"
" pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
gettext_noop("Schema"),
@@ -3418,6 +3457,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("foreign table"),
gettext_noop("table"), /* partitioned table */
gettext_noop("index"), /* partitioned index */
+ gettext_noop("schema variable"),
gettext_noop("Type"),
gettext_noop("Owner"));
@@ -3471,6 +3511,8 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "'s',"); /* was RELKIND_SPECIAL */
if (showForeign)
appendPQExpBufferStr(&buf, CppAsString2(RELKIND_FOREIGN_TABLE) ",");
+ if (showVariables)
+ appendPQExpBufferStr(&buf, CppAsString2(RELKIND_VARIABLE) ",");
appendPQExpBufferStr(&buf, "''"); /* dummy */
appendPQExpBufferStr(&buf, ")\n");
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index a4cc5efae0..c5f107d814 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -60,7 +60,7 @@ extern bool listTSTemplates(const char *pattern, bool verbose);
/* \l */
extern bool listAllDbs(const char *pattern, bool verbose);
-/* \dt, \di, \ds, \dS, etc. */
+/* \dt, \di, \ds, \dS, \dvar etc. */
extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
/* \dD */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 702e742af4..2da50f7290 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -167,7 +167,7 @@ slashUsage(unsigned short int pager)
* Use "psql --help=commands | wc" to count correctly. It's okay to count
* the USE_READLINE line even in builds without that.
*/
- output = PageOutput(125, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(126, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("General\n"));
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
@@ -257,6 +257,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
fprintf(output, _(" \\du[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
+ fprintf(output, _(" \\dV[S+] [PATTERN] list schema variables\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
fprintf(output, _(" \\dy [PATTERN] list event triggers\n"));
fprintf(output, _(" \\l[+] [PATTERN] list databases\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8bc4a194a5..ba5f6b0832 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -679,6 +679,20 @@ static const SchemaQuery Query_for_list_of_statistics = {
NULL
};
+static const SchemaQuery Query_for_list_of_variables = {
+ /* catname */
+ "pg_catalog.pg_class c",
+ /* selcondition */
+ "c.relkind IN ('V')",
+ /* viscondition */
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ /* namespace */
+ "c.relnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.relname)",
+ /* qualresult */
+ NULL
+};
/*
* Queries to get lists of names of various kinds of things, possibly
@@ -1108,6 +1122,7 @@ static const pgsql_thing_t words_after_create[] = {
* TABLE ... */
{"USER", Query_for_list_of_roles " UNION SELECT 'MAPPING FOR'"},
{"USER MAPPING FOR", NULL, NULL},
+ {"VARIABLE", NULL, &Query_for_list_of_variables},
{"VIEW", NULL, &Query_for_list_of_views},
{NULL} /* end of list */
};
@@ -1460,7 +1475,7 @@ psql_completion(const char *text, int start, int end)
"ABORT", "ALTER", "ANALYZE", "BEGIN", "CALL", "CHECKPOINT", "CLOSE", "CLUSTER",
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN",
- "FETCH", "GRANT", "IMPORT", "INSERT", "LISTEN", "LOAD", "LOCK",
+ "FETCH", "GRANT", "IMPORT", "INSERT", "LET", "LISTEN", "LOAD", "LOCK",
"MOVE", "NOTIFY", "PREPARE",
"REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE",
"RESET", "REVOKE", "ROLLBACK",
@@ -1479,7 +1494,7 @@ psql_completion(const char *text, int start, int end)
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp",
"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
- "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
+ "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\dvar",
"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
"\\endif", "\\errverbose", "\\ev",
"\\f",
@@ -2684,6 +2699,14 @@ psql_completion(const char *text, int start, int end)
else if (Matches4("CREATE", "ROLE|USER|GROUP", MatchAny, "IN"))
COMPLETE_WITH_LIST2("GROUP", "ROLE");
+/* CREATE VARIABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
+ /* Complete CREATE VARIABLE <name> with AS */
+ else if (TailMatches3("CREATE", "VARIABLE", MatchAny))
+ COMPLETE_WITH_CONST("AS");
+ /* Complete CREATE VARIABLE <name> with AS types*/
+ else if (TailMatches4("CREATE", "VARIABLE", MatchAny, "AS"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
+
/* CREATE VIEW --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete CREATE VIEW <name> with AS */
else if (TailMatches3("CREATE", "VIEW", MatchAny))
@@ -2839,6 +2862,12 @@ psql_completion(const char *text, int start, int end)
else if (Matches5("DROP", "RULE", MatchAny, "ON", MatchAny))
COMPLETE_WITH_LIST2("CASCADE", "RESTRICT");
+ /* DROP VARIABLE */
+ else if (Matches2("DROP", "VARIABLE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL);
+ else if (Matches3("DROP", "VARIABLE", MatchAny))
+ COMPLETE_WITH_LIST2("CASCADE", "RESTRICT");
+
/* EXECUTE */
else if (Matches1("EXECUTE"))
COMPLETE_WITH_QUERY(Query_for_list_of_prepared_statements);
@@ -2849,14 +2878,14 @@ psql_completion(const char *text, int start, int end)
* Complete EXPLAIN [ANALYZE] [VERBOSE] with list of EXPLAIN-able commands
*/
else if (Matches1("EXPLAIN"))
- COMPLETE_WITH_LIST7("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
- "ANALYZE", "VERBOSE");
+ COMPLETE_WITH_LIST8("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
+ "ANALYZE", "VERBOSE", "LET");
else if (Matches2("EXPLAIN", "ANALYZE"))
- COMPLETE_WITH_LIST6("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
- "VERBOSE");
+ COMPLETE_WITH_LIST7("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
+ "VERBOSE", "LET");
else if (Matches2("EXPLAIN", "VERBOSE") ||
Matches3("EXPLAIN", "ANALYZE", "VERBOSE"))
- COMPLETE_WITH_LIST5("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE");
+ COMPLETE_WITH_LIST6("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "LET");
/* FETCH && MOVE */
/* Complete FETCH with one of FORWARD, BACKWARD, RELATIVE */
@@ -2965,6 +2994,7 @@ psql_completion(const char *text, int start, int end)
" UNION SELECT 'ALL ROUTINES IN SCHEMA'"
" UNION SELECT 'ALL SEQUENCES IN SCHEMA'"
" UNION SELECT 'ALL TABLES IN SCHEMA'"
+ " UNION SELECT 'ALL VARIABLES IN SCHEMA'"
" UNION SELECT 'DATABASE'"
" UNION SELECT 'DOMAIN'"
" UNION SELECT 'FOREIGN DATA WRAPPER'"
@@ -2978,14 +3008,16 @@ psql_completion(const char *text, int start, int end)
" UNION SELECT 'SEQUENCE'"
" UNION SELECT 'TABLE'"
" UNION SELECT 'TABLESPACE'"
- " UNION SELECT 'TYPE'");
+ " UNION SELECT 'TYPE'"
+ " UNION SELECT 'VARIABLE'");
}
else if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", "ALL"))
- COMPLETE_WITH_LIST5("FUNCTIONS IN SCHEMA",
+ COMPLETE_WITH_LIST6("FUNCTIONS IN SCHEMA",
"PROCEDURES IN SCHEMA",
"ROUTINES IN SCHEMA",
"SEQUENCES IN SCHEMA",
- "TABLES IN SCHEMA");
+ "TABLES IN SCHEMA",
+ "VARIABLES IN SCHEMA");
else if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", "FOREIGN"))
COMPLETE_WITH_LIST2("DATA WRAPPER", "SERVER");
@@ -3015,6 +3047,8 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL);
else if (TailMatches1("TABLE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, NULL);
+ else if (TailMatches1("VARIABLE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL);
else if (TailMatches1("TABLESPACE"))
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
else if (TailMatches1("TYPE"))
@@ -3171,7 +3205,7 @@ psql_completion(const char *text, int start, int end)
/* PREPARE xx AS */
else if (Matches3("PREPARE", MatchAny, "AS"))
- COMPLETE_WITH_LIST4("SELECT", "UPDATE", "INSERT", "DELETE FROM");
+ COMPLETE_WITH_LIST5("SELECT", "UPDATE", "INSERT", "DELETE FROM", "LET");
/*
* PREPARE TRANSACTION is missing on purpose. It's intended for transaction
@@ -3390,6 +3424,14 @@ psql_completion(const char *text, int start, int end)
else if (TailMatches4("UPDATE", MatchAny, "SET", MatchAny))
COMPLETE_WITH_CONST("=");
+/* LET --- can be inside EXPLAIN, PREPARE etc */
+ /* If prev. word is LET suggest a list of variables */
+ else if (TailMatches1("LET"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL);
+ /* Complete LET <variable> with "=" */
+ else if (TailMatches2("LET", MatchAny))
+ COMPLETE_WITH_CONST("=");
+
/* USER MAPPING */
else if (Matches3("ALTER|CREATE|DROP", "USER", "MAPPING"))
COMPLETE_WITH_CONST("FOR");
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 26b1866c69..c5146fc138 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -167,6 +167,7 @@ DESCR("");
#define RELKIND_FOREIGN_TABLE 'f' /* foreign table */
#define RELKIND_PARTITIONED_TABLE 'p' /* partitioned table */
#define RELKIND_PARTITIONED_INDEX 'I' /* partitioned index */
+#define RELKIND_VARIABLE 'V' /* schema variable */
#define RELPERSISTENCE_PERMANENT 'p' /* regular table */
#define RELPERSISTENCE_UNLOGGED 'u' /* unlogged permanent table */
diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h
index 11b306037d..13232d7a43 100644
--- a/src/include/catalog/pg_default_acl.h
+++ b/src/include/catalog/pg_default_acl.h
@@ -71,5 +71,6 @@ typedef FormData_pg_default_acl *Form_pg_default_acl;
#define DEFACLOBJ_FUNCTION 'f' /* function */
#define DEFACLOBJ_TYPE 'T' /* type */
#define DEFACLOBJ_NAMESPACE 'n' /* namespace */
+#define DEFACLOBJ_VARIABLE 'V' /* variable */
#endif /* PG_DEFAULT_ACL_H */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f01648c961..600d3d5849 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5533,6 +5533,12 @@ DESCR("list of files in the WAL directory");
DATA(insert OID = 5028 ( satisfies_hash_partition PGNSP PGUID 12 1 0 2276 0 f f f f f f i s 4 0 16 "26 23 23 2276" _null_ "{i,i,i,v}" _null_ _null_ _null_ satisfies_hash_partition _null_ _null_ _null_ ));
DESCR("hash partition CHECK constraint");
+/* schema variables function interface */
+DATA(insert OID = 6122 ( get_schema_variable PGNSP PGUID 12 1 0 0 0 f f f f f f v r 2 0 2283 "2205 2283" _null_ _null_ _null_ _null_ _null_ get_schema_variable _null_ _null_ _null_ ));
+DESCR("returns value of schema variable");
+DATA(insert OID = 6123 ( set_schema_variable PGNSP PGUID 12 1 0 0 0 f f f f f f v r 2 0 2278 "2205 2283" _null_ _null_ _null_ _null_ _null_ set_schema_variable _null_ _null_ _null_ ));
+DESCR("returns value of schema variable");
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
diff --git a/src/include/commands/schemavar.h b/src/include/commands/schemavar.h
new file mode 100644
index 0000000000..6f65b1f1d3
--- /dev/null
+++ b/src/include/commands/schemavar.h
@@ -0,0 +1,31 @@
+/*-------------------------------------------------------------------------
+ *
+ * schemavar.h
+ * prototypes for schemavar.c.
+ *
+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/commands/schemavar.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef SCHEMAVAR_H
+#define SCHEMAVAR_H
+
+#include "catalog/objectaddress.h"
+#include "nodes/parsenodes.h"
+#include "parser/parse_node.h"
+
+extern ObjectAddress DefineSchemaVariable(ParseState *pstate, CreateSchemaVarStmt *var);
+
+extern void ResetSchemaVariablesCache(void);
+
+extern char *get_schemavar_name(Oid varid);
+
+extern void SetSchemaVariable(Oid varid, Datum value, bool isNull, Oid typid, int32 typmod, int16 typlen, bool typbyval);
+extern Datum GetSchemaVariable(Oid varid, bool *isNull, Oid typid, int32 typmod, int16 typlen, bool typbyval);
+extern void SetSchemaVariableSecure(Oid varid, Datum value, bool isNull, Oid typid, int32 typmod, int16 typlen, bool typbyval);
+extern Datum GetSchemaVariableSecure(Oid varid, bool *isNull, Oid typid, int32 typmod, int16 typlen, bool typbyval);
+
+#endif
\ No newline at end of file
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 117fc892f4..a282f1e4e0 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -138,6 +138,7 @@ typedef enum ExprEvalOp
EEOP_PARAM_EXEC,
EEOP_PARAM_EXTERN,
EEOP_PARAM_CALLBACK,
+ EEOP_PARAM_SCHEMA_VARIABLE,
/* return CaseTestExpr value */
EEOP_CASE_TESTVAL,
@@ -342,11 +343,14 @@ typedef struct ExprEvalStep
TupleDesc argdesc;
} nulltest_row;
- /* for EEOP_PARAM_EXEC/EXTERN */
+ /* for EEOP_PARAM_EXEC/EXTERN/VARIABLE */
struct
{
- int paramid; /* numeric ID for parameter */
- Oid paramtype; /* OID of parameter's datatype */
+ int paramid; /* numeric ID for parameter */
+ Oid paramtype; /* OID of parameter's datatype */
+ int32 paramtypmod; /* typmod of param (not used yet) */
+ int16 paramtyplen; /* expected length */
+ bool paramtypbyval; /* is passed by value */
} param;
/* for EEOP_PARAM_CALLBACK */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 74b094a9c3..2f4986099d 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -344,6 +344,7 @@ typedef enum NodeTag
T_CreateTableAsStmt,
T_CreateSeqStmt,
T_AlterSeqStmt,
+ T_CreateSchemaVarStmt,
T_VariableSetStmt,
T_VariableShowStmt,
T_DiscardStmt,
@@ -415,6 +416,7 @@ typedef enum NodeTag
T_CreateStatsStmt,
T_AlterCollationStmt,
T_CallStmt,
+ T_LetStmt,
/*
* TAGS FOR PARSE TREE NODES (parsenodes.h)
@@ -657,6 +659,7 @@ typedef enum CmdType
CMD_UPDATE, /* update stmt */
CMD_INSERT, /* insert stmt */
CMD_DELETE,
+ CMD_LET,
CMD_UTILITY, /* cmds like create, destroy, copy, vacuum,
* etc. */
CMD_NOTHING /* dummy command for instead nothing rules
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a16de289ba..d1d03c9cbe 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1488,6 +1488,14 @@ typedef struct UpdateStmt
WithClause *withClause; /* WITH clause */
} UpdateStmt;
+typedef struct LetStmt
+{
+ NodeTag type;
+ RangeVar *variable; /* relation to insert into */
+ Node *selectStmt; /* the source SELECT/VALUES, or NULL */
+} LetStmt;
+
+
/* ----------------------
* Select Statement
*
@@ -1665,6 +1673,7 @@ typedef enum ObjectType
OBJECT_TSTEMPLATE,
OBJECT_TYPE,
OBJECT_USER_MAPPING,
+ OBJECT_VARIABLE,
OBJECT_VIEW
} ObjectType;
@@ -2478,6 +2487,18 @@ typedef struct AlterSeqStmt
} AlterSeqStmt;
/* ----------------------
+ * Create VARIABLE Statement
+ * ----------------------
+ */
+typedef struct CreateSchemaVarStmt
+{
+ NodeTag type;
+ RangeVar *variable; /* the variable to create */
+ TypeName *typeName; /* the variable type */
+ bool if_not_exists; /* just do nothing if it already exists? */
+} CreateSchemaVarStmt;
+
+/* ----------------------
* Create {Aggregate|Operator|Type} Statement
* ----------------------
*/
@@ -3206,7 +3227,8 @@ typedef enum DiscardMode
DISCARD_ALL,
DISCARD_PLANS,
DISCARD_SEQUENCES,
- DISCARD_TEMP
+ DISCARD_TEMP,
+ DISCARD_VARIABLES
} DiscardMode;
typedef struct DiscardStmt
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 1b4b0d75af..b366471940 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -229,13 +229,17 @@ typedef struct Const
* of the `paramid' field contain the SubLink's subLinkId, and
* the low-order 16 bits contain the column number. (This type
* of Param is also converted to PARAM_EXEC during planning.)
+ *
+ * PARAM_SCHEMA_VARIABLE: The parameter is a access to schema variable
+ * paramid holds varid.
*/
typedef enum ParamKind
{
PARAM_EXTERN,
PARAM_EXEC,
PARAM_SUBLINK,
- PARAM_MULTIEXPR
+ PARAM_MULTIEXPR,
+ PARAM_SCHEMA_VARIABLE
} ParamKind;
typedef struct Param
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 26af944e03..3971d7478b 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -229,6 +229,7 @@ PG_KEYWORD("leading", LEADING, RESERVED_KEYWORD)
PG_KEYWORD("leakproof", LEAKPROOF, UNRESERVED_KEYWORD)
PG_KEYWORD("least", LEAST, COL_NAME_KEYWORD)
PG_KEYWORD("left", LEFT, TYPE_FUNC_NAME_KEYWORD)
+PG_KEYWORD("let", LET, UNRESERVED_KEYWORD)
PG_KEYWORD("level", LEVEL, UNRESERVED_KEYWORD)
PG_KEYWORD("like", LIKE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("limit", LIMIT, RESERVED_KEYWORD)
@@ -430,6 +431,8 @@ PG_KEYWORD("validator", VALIDATOR, UNRESERVED_KEYWORD)
PG_KEYWORD("value", VALUE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("values", VALUES, COL_NAME_KEYWORD)
PG_KEYWORD("varchar", VARCHAR, COL_NAME_KEYWORD)
+PG_KEYWORD("variable", VARIABLE, UNRESERVED_KEYWORD)
+PG_KEYWORD("variables", VARIABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 4e96fa7907..18ba221180 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -134,6 +134,8 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param,
* to process them like UPDATE. (Note this can change intra-statement, for
* cases like INSERT ON CONFLICT UPDATE.)
*
+ * p_is_let: true to process assignment expressions like LET.
+ *
* p_windowdefs: list of WindowDefs representing WINDOW and OVER clauses.
* We collect these while transforming expressions and then transform them
* afterwards (so that any resjunk tlist items needed for the sort/group
@@ -183,6 +185,7 @@ struct ParseState
Relation p_target_relation; /* INSERT/UPDATE/DELETE target rel */
RangeTblEntry *p_target_rangetblentry; /* target rel's RTE */
bool p_is_insert; /* process assignment like INSERT not UPDATE */
+ bool p_is_let; /* process assignment LET stmt */
List *p_windowdefs; /* raw representations of window clauses */
ParseExprKind p_expr_kind; /* what kind of expression we're parsing */
int p_next_resno; /* next targetlist resno to assign */
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index b9792acdae..760aaed9a8 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -129,4 +129,6 @@ extern Oid attnumTypeId(Relation rd, int attid);
extern Oid attnumCollationId(Relation rd, int attid);
extern bool isQueryUsingTempRelation(Query *query);
+extern Node *toSchemaVariable(ParseState *pstate, char *nspname, char *varname, int location);
+
#endif /* PARSE_RELATION_H */
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index f4d4be8d0d..d0737a9e4b 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -166,6 +166,7 @@ typedef ArrayType Acl;
#define ACL_ALL_RIGHTS_SCHEMA (ACL_USAGE|ACL_CREATE)
#define ACL_ALL_RIGHTS_TABLESPACE (ACL_CREATE)
#define ACL_ALL_RIGHTS_TYPE (ACL_USAGE)
+#define ACL_ALL_RIGHTS_VARIABLE (ACL_SELECT|ACL_UPDATE)
/* operation codes for pg_*_aclmask */
typedef enum
diff --git a/src/test/regress/expected/schema_variables.out b/src/test/regress/expected/schema_variables.out
new file mode 100644
index 0000000000..ad700c15d8
--- /dev/null
+++ b/src/test/regress/expected/schema_variables.out
@@ -0,0 +1,236 @@
+CREATE VARIABLE var1 AS integer;
+CREATE TEMP VARIABLE var2 AS text;
+-- should to fail
+CREATE VARIABLE var2 AS pg_class;
+ERROR: Composite types are not allowed as variable type.
+DROP VARIABLE var1, var2;
+-- functional interface, attention typmod is not stored
+CREATE VARIABLE var1 AS numeric(10,1);
+SELECT set_schema_variable('var1', 333);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 333
+(1 row)
+
+SELECT set_schema_variable('var1', 333::integer);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 333
+(1 row)
+
+SELECT set_schema_variable('var1', '333.55'::text);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 333.55
+(1 row)
+
+SELECT get_schema_variable('var1', null::int);
+ get_schema_variable
+---------------------
+ 334
+(1 row)
+
+SELECT get_schema_variable('var1', null::text);
+ get_schema_variable
+---------------------
+ 333.55
+(1 row)
+
+-- access rights test
+CREATE ROLE var_test_role;
+SET ROLE TO var_test_role;
+-- should to fail
+SELECT set_schema_variable('var1', '1000'::text);
+ERROR: permission denied for schema variable var1
+SELECT get_schema_variable('var1', null::numeric);
+ERROR: permission denied for schema variable var1
+SET ROLE TO DEFAULT;
+GRANT SELECT ON VARIABLE var1 TO var_test_role;
+SET ROLE TO var_test_role;
+-- should to fail
+SELECT set_schema_variable('var1', '1000'::text);
+ERROR: permission denied for schema variable var1
+-- should to work
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 333.55
+(1 row)
+
+SET ROLE TO DEFAULT;
+GRANT UPDATE ON VARIABLE var1 TO var_test_role;
+SET ROLE TO var_test_role;
+-- should to work
+SELECT set_schema_variable('var1', '1000'::text);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 1000
+(1 row)
+
+SET ROLE TO DEFAULT;
+REVOKE ALL ON VARIABLE var1 FROM var_test_role;
+CREATE VARIABLE var AS integer;
+SELECT set_schema_variable('public.var', 1234);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT public.var;
+ var
+------
+ 1234
+(1 row)
+
+DO $$
+BEGIN
+ RAISE NOTICE 'public.var is = %', public.var;
+END;
+$$;
+NOTICE: public.var is = 1234
+CREATE OR REPLACE FUNCTION secure_var()
+RETURNS int AS $$
+ SELECT public.var;
+$$ LANGUAGE sql SECURITY DEFINER;
+SELECT secure_var();
+ secure_var
+------------
+ 1234
+(1 row)
+
+SET ROLE TO var_test_role;
+-- should to fail
+SELECT public.var;
+ERROR: permission denied for schema variable var
+-- should to work;
+SELECT secure_var();
+ secure_var
+------------
+ 1234
+(1 row)
+
+SET ROLE TO DEFAULT;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v = var;
+ QUERY PLAN
+-----------------------------------------------
+ Function Scan on pg_catalog.generate_series g
+ Output: v
+ Function Call: generate_series(1, 100)
+ Filter: (g.v = var)
+(4 rows)
+
+CREATE VIEW schema_var_view AS SELECT var;
+SELECT * FROM schema_var_view;
+ var
+------
+ 1234
+(1 row)
+
+\c -
+-- should to work still, but var will be empty
+SELECT * FROM schema_var_view;
+ var
+-----
+
+(1 row)
+
+LET var1 = pi();
+SELECT var1;
+ var1
+------------------
+ 3.14159265358979
+(1 row)
+
+-- we can look on execution plan
+EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi();
+ QUERY PLAN
+------------------------------------------------------
+ Let on public.var1
+ -> Result
+ Output: '3.14159265358979'::double precision
+(3 rows)
+
+-- LET can be prepared
+PREPARE var_pp(int, numeric) AS LET var1 = $1 + $2;
+EXECUTE var_pp(100, 1.23456);
+SELECT var1;
+ var1
+-----------
+ 101.23456
+(1 row)
+
+CREATE VARIABLE var3 AS int;
+CREATE OR REPLACE FUNCTION inc(int)
+RETURNS int AS $$
+BEGIN
+ LET public.var3 = COALESCE(public.var3 + $1, $1);
+ RETURN var3;
+END;
+$$ LANGUAGE plpgsql;
+SELECT inc(1);
+ inc
+-----
+ 1
+(1 row)
+
+SELECT inc(1);
+ inc
+-----
+ 2
+(1 row)
+
+SELECT inc(1);
+ inc
+-----
+ 3
+(1 row)
+
+SELECT inc(1) FROM generate_series(1,10);
+ inc
+-----
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+(10 rows)
+
+SET ROLE TO var_test_role;
+-- should to fail
+LET var3 = 0;
+ERROR: permission denied for schema variable var3
+SET ROLE TO DEFAULT;
+DROP VIEW schema_var_view;
+DROP ROLE var_test_role;
+DROP VARIABLE var CASCADE;
+DROP VARIABLE var1 CASCADE;
+DROP VARIABLE var3 CASCADE;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index ad9434fb87..33fe7ee476 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -111,7 +111,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# NB: temp.sql does a reconnect which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml schema_variables
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 27cd49845e..22c4cac7ce 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -187,3 +187,4 @@ test: hash_part
test: indexing
test: event_trigger
test: stats
+test: schema_variables
diff --git a/src/test/regress/sql/schema_variables.sql b/src/test/regress/sql/schema_variables.sql
new file mode 100644
index 0000000000..9ee9e174f9
--- /dev/null
+++ b/src/test/regress/sql/schema_variables.sql
@@ -0,0 +1,139 @@
+CREATE VARIABLE var1 AS integer;
+CREATE TEMP VARIABLE var2 AS text;
+
+-- should to fail
+CREATE VARIABLE var2 AS pg_class;
+
+DROP VARIABLE var1, var2;
+
+-- functional interface, attention typmod is not stored
+CREATE VARIABLE var1 AS numeric(10,1);
+SELECT set_schema_variable('var1', 333);
+SELECT get_schema_variable('var1', null::numeric);
+
+SELECT set_schema_variable('var1', 333::integer);
+SELECT get_schema_variable('var1', null::numeric);
+
+SELECT set_schema_variable('var1', '333.55'::text);
+SELECT get_schema_variable('var1', null::numeric);
+SELECT get_schema_variable('var1', null::int);
+SELECT get_schema_variable('var1', null::text);
+
+-- access rights test
+
+CREATE ROLE var_test_role;
+
+SET ROLE TO var_test_role;
+
+-- should to fail
+SELECT set_schema_variable('var1', '1000'::text);
+SELECT get_schema_variable('var1', null::numeric);
+
+SET ROLE TO DEFAULT;
+
+GRANT SELECT ON VARIABLE var1 TO var_test_role;
+
+SET ROLE TO var_test_role;
+-- should to fail
+SELECT set_schema_variable('var1', '1000'::text);
+-- should to work
+SELECT get_schema_variable('var1', null::numeric);
+
+SET ROLE TO DEFAULT;
+
+GRANT UPDATE ON VARIABLE var1 TO var_test_role;
+
+SET ROLE TO var_test_role;
+
+-- should to work
+SELECT set_schema_variable('var1', '1000'::text);
+SELECT get_schema_variable('var1', null::numeric);
+
+SET ROLE TO DEFAULT;
+
+REVOKE ALL ON VARIABLE var1 FROM var_test_role;
+
+CREATE VARIABLE var AS integer;
+
+SELECT set_schema_variable('public.var', 1234);
+
+SELECT public.var;
+
+DO $$
+BEGIN
+ RAISE NOTICE 'public.var is = %', public.var;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION secure_var()
+RETURNS int AS $$
+ SELECT public.var;
+$$ LANGUAGE sql SECURITY DEFINER;
+
+SELECT secure_var();
+
+SET ROLE TO var_test_role;
+
+-- should to fail
+SELECT public.var;
+
+-- should to work;
+SELECT secure_var();
+
+SET ROLE TO DEFAULT;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v = var;
+
+CREATE VIEW schema_var_view AS SELECT var;
+
+SELECT * FROM schema_var_view;
+
+\c -
+
+-- should to work still, but var will be empty
+SELECT * FROM schema_var_view;
+
+LET var1 = pi();
+
+SELECT var1;
+
+-- we can look on execution plan
+EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi();
+
+-- LET can be prepared
+PREPARE var_pp(int, numeric) AS LET var1 = $1 + $2;
+
+EXECUTE var_pp(100, 1.23456);
+
+SELECT var1;
+
+CREATE VARIABLE var3 AS int;
+
+CREATE OR REPLACE FUNCTION inc(int)
+RETURNS int AS $$
+BEGIN
+ LET public.var3 = COALESCE(public.var3 + $1, $1);
+ RETURN var3;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT inc(1);
+SELECT inc(1);
+SELECT inc(1);
+
+SELECT inc(1) FROM generate_series(1,10);
+
+SET ROLE TO var_test_role;
+
+-- should to fail
+LET var3 = 0;
+
+SET ROLE TO DEFAULT;
+
+DROP VIEW schema_var_view;
+
+DROP ROLE var_test_role;
+
+DROP VARIABLE var CASCADE;
+DROP VARIABLE var1 CASCADE;
+DROP VARIABLE var3 CASCADE;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index d4765ce3b0..b0404c21a5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -423,6 +423,7 @@ CreateReplicationSlotCmd
CreateRoleStmt
CreateSchemaStmt
CreateSchemaStmtContext
+CreateSchemaVarStmt
CreateSeqStmt
CreateStatsStmt
CreateStmt
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.g.johnston@gmail.com, pavel.stehule@gmail.com, pavel@gf.microolap.com, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] proposal: schema variables
In-Reply-To: <CAKFQuwa00-4HTujbnYNy_OdZ2OfjUO3AX4R91DbQ1wPBZegCfg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox