[patch] [doc] Minor variable related cleanup and rewording of plpgsql docs
Hackers,
Bug # 16519 [1]/messages/by-id/16519-9ef04828d058a319@postgresql.org is another report of confusion regarding trying to use
parameters in improper locations - specifically the SET ROLE command within
pl/pgsql. I'm re-attaching the doc patch and am adding it to the
commitfest.
David J.
[1]: /messages/by-id/16519-9ef04828d058a319@postgresql.org
/messages/by-id/16519-9ef04828d058a319@postgresql.org
Attachments:
v1-doc-plpgsql-variable-usage-cleanup.patchapplication/octet-stream; name=v1-doc-plpgsql-variable-usage-cleanup.patchDownload
commit 678834d2fc1d74710ca36b075be0989832e15cec
Author: David G. Johnston <david.g.johnston@gmail.com>
Date: Fri Oct 2 20:02:00 2020 +0000
Minor cleanup and rewording of plpgsql docs
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 815912666d..4af66a637c 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -856,7 +856,7 @@ SELECT <replaceable>expression</replaceable>
</synopsis>
to the main SQL engine. While forming the <command>SELECT</command> command,
any occurrences of <application>PL/pgSQL</application> variable names
- are replaced by parameters, as discussed in detail in
+ are replaced by query parameters, as discussed in detail in
<xref linkend="plpgsql-var-subst"/>.
This allows the query plan for the <command>SELECT</command> to
be prepared just once and then reused for subsequent
@@ -945,11 +945,13 @@ my_record.user_id := 20;
</para>
<para>
- Any <application>PL/pgSQL</application> variable name appearing
- in the command text is treated as a parameter, and then the
+ Any <application>PL/pgSQL</application> variables appearing
+ in the command text are replaced by query parameters, and then the
current value of the variable is provided as the parameter value
at run time. This is exactly like the processing described earlier
for expressions; for details see <xref linkend="plpgsql-var-subst"/>.
+ If the SQL command being executed is incapable of returning a result
+ it does not accept query parameters.
</para>
<para>
@@ -2507,7 +2509,7 @@ $$ LANGUAGE plpgsql;
</para>
<para>
- <application>PL/pgSQL</application> variables are substituted into the query text,
+ <application>PL/pgSQL</application> variables are converted into query parameters,
and the query plan is cached for possible re-use, as discussed in
detail in <xref linkend="plpgsql-var-subst"/> and
<xref linkend="plpgsql-plan-caching"/>.
@@ -4583,9 +4585,9 @@ CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
SQL statements and expressions within a <application>PL/pgSQL</application> function
can refer to variables and parameters of the function. Behind the scenes,
<application>PL/pgSQL</application> substitutes query parameters for such references.
- Parameters will only be substituted in places where a parameter or
- column reference is syntactically allowed. As an extreme case, consider
- this example of poor programming style:
+ Query parameters will only be substituted in places where syntactically allowed
+ (in particular, identifiers can never be replaced with a query parameter.)
+ As an extreme case, consider this example of poor programming style:
<programlisting>
INSERT INTO foo (foo) VALUES (foo);
</programlisting>
@@ -4597,13 +4599,6 @@ INSERT INTO foo (foo) VALUES (foo);
variable.
</para>
- <note>
- <para>
- <productname>PostgreSQL</productname> versions before 9.0 would try
- to substitute the variable in all three cases, leading to syntax errors.
- </para>
- </note>
-
<para>
Since the names of variables are syntactically no different from the names
of table columns, there can be ambiguity in statements that also refer to
@@ -5253,13 +5248,14 @@ HINT: Make sure the query returns the exact list of columns.
<itemizedlist>
<listitem>
<para>
- If a name used in a SQL command could be either a column name of a
- table or a reference to a variable of the function,
- <application>PL/SQL</application> treats it as a column name. This corresponds
- to <application>PL/pgSQL</application>'s
+ If a name used in a SQL command matches a known column name and also
+ a named variable within the function,
+ <application>PL/SQL</application> treats it as a column name.
+ By default, <application>PL/pgSQL</application> will treat it as a variable,
+ but one can specify
<literal>plpgsql.variable_conflict</literal> = <literal>use_column</literal>
- behavior, which is not the default,
- as explained in <xref linkend="plpgsql-var-subst"/>.
+ (see <xref linkend="plpgsql-var-subst"/> for specifics)
+ to change the behavior to match <application>PL/SQL</application>.
It's often best to avoid such ambiguities in the first place,
but if you have to port a large amount of code that depends on
this behavior, setting <literal>variable_conflict</literal> may be the
čt 26. 11. 2020 v 6:41 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:
Hackers,
Bug # 16519 [1] is another report of confusion regarding trying to use
parameters in improper locations - specifically the SET ROLE command within
pl/pgsql. I'm re-attaching the doc patch and am adding it to the
commitfest.
I checked this patch, and I think so it is correct - my comments are just
about enhancing by some examples
Maybe for following sentence the some examples can be practical
+ If the SQL command being executed is incapable of returning a result
+ it does not accept query parameters.
</para>+ it does not accept query parameters (usually DDL commands like CREATE
TABLE, DROP TABLE, ALTER ... ).
+ Query parameters will only be substituted in places where
syntactically allowed
+ (in particular, identifiers can never be replaced with a query
parameter.)
+ As an extreme case, consider this example of poor programming style:In this case, I miss the more precious specification of identifiers
+ (in particular, SQL identifiers (like schema, table, column names) can
never be replaced with a query parameter.)
Regards
Pavel
Show quoted text
David J.
On Thu, Nov 26, 2020 at 12:49 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
čt 26. 11. 2020 v 6:41 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:Hackers,
Bug # 16519 [1] is another report of confusion regarding trying to use
parameters in improper locations - specifically the SET ROLE command within
pl/pgsql. I'm re-attaching the doc patch and am adding it to the
commitfest.I checked this patch, and I think so it is correct - my comments are just
about enhancing by some examples
Thank you for the review.
v2 attached.
I added examples in the two places you noted.
Upon re-reading, I decided that opening up the section by including
everything then fitting in parameters with an exception for utility
commands (without previously/otherwise identifying them) forced some
undesirable verbosity. Instead, I opened up with the utility commands as
the main body of non-result returning commands and then moved onto
delete/insert/update non-returning cases when the subsequent paragraph
regarding parameters can then refer to the second class (by way of
excluding the first class). This seems to flow better, IMO.
David J.
Attachments:
v2-doc-plpgsql-variable-usage-cleanup.patchapplication/octet-stream; name=v2-doc-plpgsql-variable-usage-cleanup.patchDownload
commit da8f0114fc9a661c001e49df540ccaa35dbbdb29
Author: David G. Johnston <david.g.johnston@gmail.com>
Date: Mon Nov 30 03:17:26 2020 +0000
Minor cleanup and rewording of plpgsql docs
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 9ec168b0c4..ffe124ab54 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -894,7 +894,7 @@ SELECT <replaceable>expression</replaceable>
</synopsis>
to the main SQL engine. While forming the <command>SELECT</command> command,
any occurrences of <application>PL/pgSQL</application> variable names
- are replaced by parameters, as discussed in detail in
+ are replaced by query parameters, as discussed in detail in
<xref linkend="plpgsql-var-subst"/>.
This allows the query plan for the <command>SELECT</command> to
be prepared just once and then reused for subsequent
@@ -976,15 +976,23 @@ my_record.user_id := 20;
<title>Executing a Command with No Result</title>
<para>
- For any SQL command that does not return rows, for example
- <command>INSERT</command> without a <literal>RETURNING</literal> clause, you can
- execute the command within a <application>PL/pgSQL</application> function
- just by writing the command.
+ The utility SQL commands, which are incapable of returning results,
+ (e.g., DDL commands like CREATE TABLE and DROP TYPE,
+ and the environment changing command SET), are executed just by
+ writing the command.
</para>
<para>
- Any <application>PL/pgSQL</application> variable name appearing
- in the command text is treated as a parameter, and then the
+ You can also execute a <command>DELETE</command>, <command>INSERT</command>,
+ or <command>UPDATE</command> SQL command, without a <literal>RETURNING</literal>
+ clause, just by writing the command. Adding the <literal>RETURNING</literal>
+ makes the command behave like <command>SELECT</command>, which is described
+ in the next section.
+ </para>
+
+ <para>
+ For the non-utility commands, any <application>PL/pgSQL</application> variables
+ appearing in the command text are replaced by query parameters, and then the
current value of the variable is provided as the parameter value
at run time. This is exactly like the processing described earlier
for expressions; for details see <xref linkend="plpgsql-var-subst"/>.
@@ -2545,7 +2553,7 @@ $$ LANGUAGE plpgsql;
</para>
<para>
- <application>PL/pgSQL</application> variables are substituted into the query text,
+ <application>PL/pgSQL</application> variables are converted into query parameters,
and the query plan is cached for possible re-use, as discussed in
detail in <xref linkend="plpgsql-var-subst"/> and
<xref linkend="plpgsql-plan-caching"/>.
@@ -4621,9 +4629,10 @@ CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
SQL statements and expressions within a <application>PL/pgSQL</application> function
can refer to variables and parameters of the function. Behind the scenes,
<application>PL/pgSQL</application> substitutes query parameters for such references.
- Parameters will only be substituted in places where a parameter or
- column reference is syntactically allowed. As an extreme case, consider
- this example of poor programming style:
+ Query parameters will only be substituted in places where syntactically allowed
+ (in particular, SQL identifiers - such as schema, table, and column names - can never
+ be replaced with a query parameter.)
+ As an extreme case, consider this example of poor programming style:
<programlisting>
INSERT INTO foo (foo) VALUES (foo);
</programlisting>
@@ -4635,13 +4644,6 @@ INSERT INTO foo (foo) VALUES (foo);
variable.
</para>
- <note>
- <para>
- <productname>PostgreSQL</productname> versions before 9.0 would try
- to substitute the variable in all three cases, leading to syntax errors.
- </para>
- </note>
-
<para>
Since the names of variables are syntactically no different from the names
of table columns, there can be ambiguity in statements that also refer to
@@ -5291,13 +5293,14 @@ HINT: Make sure the query returns the exact list of columns.
<itemizedlist>
<listitem>
<para>
- If a name used in a SQL command could be either a column name of a
- table or a reference to a variable of the function,
- <application>PL/SQL</application> treats it as a column name. This corresponds
- to <application>PL/pgSQL</application>'s
+ If a name used in a SQL command matches a known column name and also
+ a named variable within the function,
+ <application>PL/SQL</application> treats it as a column name.
+ By default, <application>PL/pgSQL</application> will treat it as a variable,
+ but one can specify
<literal>plpgsql.variable_conflict</literal> = <literal>use_column</literal>
- behavior, which is not the default,
- as explained in <xref linkend="plpgsql-var-subst"/>.
+ (see <xref linkend="plpgsql-var-subst"/> for specifics)
+ to change the behavior to match <application>PL/SQL</application>.
It's often best to avoid such ambiguities in the first place,
but if you have to port a large amount of code that depends on
this behavior, setting <literal>variable_conflict</literal> may be the
po 30. 11. 2020 v 4:24 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:
On Thu, Nov 26, 2020 at 12:49 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:čt 26. 11. 2020 v 6:41 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:Hackers,
Bug # 16519 [1] is another report of confusion regarding trying to use
parameters in improper locations - specifically the SET ROLE command within
pl/pgsql. I'm re-attaching the doc patch and am adding it to the
commitfest.I checked this patch, and I think so it is correct - my comments are just
about enhancing by some examplesThank you for the review.
v2 attached.
I added examples in the two places you noted.
Upon re-reading, I decided that opening up the section by including
everything then fitting in parameters with an exception for utility
commands (without previously/otherwise identifying them) forced some
undesirable verbosity. Instead, I opened up with the utility commands as
the main body of non-result returning commands and then moved onto
delete/insert/update non-returning cases when the subsequent paragraph
regarding parameters can then refer to the second class (by way of
excluding the first class). This seems to flow better, IMO.
I have no objections, but maybe these pages are a little bit unclear
generally, because the core of the problem is not described.
Personally I miss a description of the reason why variables cannot be used
- the description "variables cannot be used in statements without result"
is true, but it is not core information.
The important fact is usage of an execution plan or not. The statements
with an execution plan can be parametrized (DML - INSERT, UPDATE, DELETE),
and SELECT. The statements without execution plans should not be
parametrized. The only execution via execution plan executor allows
parametrization. DDL statements are executed via utility execution, and
their parameterization is not supported.
Regards
Pavel
Show quoted text
David J.
On Mon, Nov 30, 2020 at 12:51 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
po 30. 11. 2020 v 4:24 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:On Thu, Nov 26, 2020 at 12:49 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:čt 26. 11. 2020 v 6:41 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:Hackers,
Bug # 16519 [1] is another report of confusion regarding trying to use
parameters in improper locations - specifically the SET ROLE command within
pl/pgsql. I'm re-attaching the doc patch and am adding it to the
commitfest.I checked this patch, and I think so it is correct - my comments are
just about enhancing by some examplesThank you for the review.
v2 attached.
I added examples in the two places you noted.
Upon re-reading, I decided that opening up the section by including
everything then fitting in parameters with an exception for utility
commands (without previously/otherwise identifying them) forced some
undesirable verbosity. Instead, I opened up with the utility commands as
the main body of non-result returning commands and then moved onto
delete/insert/update non-returning cases when the subsequent paragraph
regarding parameters can then refer to the second class (by way of
excluding the first class). This seems to flow better, IMO.I have no objections, but maybe these pages are a little bit unclear
generally, because the core of the problem is not described.
Personally I miss a description of the reason why variables cannot be used
- the description "variables cannot be used in statements without result"
is true, but it is not core information.
In the section "executing commands that don't return results" it does seem
like core information...but I get your point.
The important fact is usage of an execution plan or not.
This is already mentioned in the linked-to section:
"Variable substitution currently works only in SELECT, INSERT, UPDATE, and
DELETE commands, because the main SQL engine allows query parameters only
in these commands. To use a non-constant name or value in other statement
types (generically called utility statements), you must construct the
utility statement as a string and EXECUTE it."
I didn't feel the need to repeat that material in full in the "no results"
section. I left that pointing out the "results" dynamic there would be
useful since the original wording seemed to forget about the presence of
utility commands altogether which was confusing for that section.
David J.
po 30. 11. 2020 v 16:06 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:
On Mon, Nov 30, 2020 at 12:51 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:po 30. 11. 2020 v 4:24 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:On Thu, Nov 26, 2020 at 12:49 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:čt 26. 11. 2020 v 6:41 odesílatel David G. Johnston <
david.g.johnston@gmail.com> napsal:Hackers,
Bug # 16519 [1] is another report of confusion regarding trying to use
parameters in improper locations - specifically the SET ROLE command within
pl/pgsql. I'm re-attaching the doc patch and am adding it to the
commitfest.I checked this patch, and I think so it is correct - my comments are
just about enhancing by some examplesThank you for the review.
v2 attached.
I added examples in the two places you noted.
Upon re-reading, I decided that opening up the section by including
everything then fitting in parameters with an exception for utility
commands (without previously/otherwise identifying them) forced some
undesirable verbosity. Instead, I opened up with the utility commands as
the main body of non-result returning commands and then moved onto
delete/insert/update non-returning cases when the subsequent paragraph
regarding parameters can then refer to the second class (by way of
excluding the first class). This seems to flow better, IMO.I have no objections, but maybe these pages are a little bit unclear
generally, because the core of the problem is not described.Personally I miss a description of the reason why variables cannot be
used - the description "variables cannot be used in statements without
result" is true, but it is not core information.In the section "executing commands that don't return results" it does seem
like core information...but I get your point.The important fact is usage of an execution plan or not.
This is already mentioned in the linked-to section:
"Variable substitution currently works only in SELECT, INSERT, UPDATE, and
DELETE commands, because the main SQL engine allows query parameters only
in these commands. To use a non-constant name or value in other statement
types (generically called utility statements), you must construct the
utility statement as a string and EXECUTE it."I didn't feel the need to repeat that material in full in the "no results"
section. I left that pointing out the "results" dynamic there would be
useful since the original wording seemed to forget about the presence of
utility commands altogether which was confusing for that section.
ok
Pavel
Show quoted text
David J.
On 11/30/20 10:37 AM, Pavel Stehule wrote:
po 30. 11. 2020 v 16:06 odesílatel David G. Johnston
ok
This patch looks reasonable to me overall.
A few comments:
1) PL/SQL seems to be used in a few places where I believe PL/pgSQL is
meant. This was pre-existing but now seems like a good opportunity to
fix it, unless I am misunderstanding.
2) I think:
+ makes the command behave like <command>SELECT</command>, which is
described
flows a little better as:
+ makes the command behave like <command>SELECT</command>, as described
Regards,
--
-David
david@pgmasters.net
út 9. 3. 2021 v 18:03 odesílatel David Steele <david@pgmasters.net> napsal:
On 11/30/20 10:37 AM, Pavel Stehule wrote:
po 30. 11. 2020 v 16:06 odesílatel David G. Johnston
ok
This patch looks reasonable to me overall.
A few comments:
1) PL/SQL seems to be used in a few places where I believe PL/pgSQL is
meant. This was pre-existing but now seems like a good opportunity to
fix it, unless I am misunderstanding.
+1
2) I think:
+ makes the command behave like <command>SELECT</command>, which is
describedflows a little better as:
+ makes the command behave like <command>SELECT</command>, as described
I am not native speaker, so I am not able to evaluate it.
Regards
Pavel
Show quoted text
Regards,
--
-David
david@pgmasters.net
David Steele <david@pgmasters.net> writes:
1) PL/SQL seems to be used in a few places where I believe PL/pgSQL is
meant. This was pre-existing but now seems like a good opportunity to
fix it, unless I am misunderstanding.
PL/SQL is Oracle's function language, which PL/pgSQL is modeled on.
At least some of the mentions of PL/SQL are probably intentional,
so you'll have to look closely not just search-and-replace.
regards, tom lane
On Tue, Mar 9, 2021 at 10:45 AM Pavel Stehule <pavel.stehule@gmail.com>
wrote:
út 9. 3. 2021 v 18:03 odesílatel David Steele <david@pgmasters.net>
napsal:On 11/30/20 10:37 AM, Pavel Stehule wrote:
po 30. 11. 2020 v 16:06 odesílatel David G. Johnston
ok
This patch looks reasonable to me overall.
A few comments:
1) PL/SQL seems to be used in a few places where I believe PL/pgSQL is
meant. This was pre-existing but now seems like a good opportunity to
fix it, unless I am misunderstanding.+1
I vaguely recall looking for this back in October and not finding anything
that needed fixing in the area I was working in. The ready-for-commit can
stand without further investigation. Feel free to look for and fix
oversights of this nature if you feel they exist.
2) I think:
+ makes the command behave like <command>SELECT</command>, which is
describedflows a little better as:
+ makes the command behave like <command>SELECT</command>, as
describedI am not native speaker, so I am not able to evaluate it.
"which is described" is perfectly valid. I don't know that "as described"
is materially better from a flow perspective (I agree it reads a tiny bit
better) but either seems to adequately communicate the intended point so I
wouldn't gripe if it was changed during commit.
I intend to leave the patch as-is though since as written it is
committable, this second comment is just style and the first is scope creep.
David J.
On 3/9/21 1:08 PM, Tom Lane wrote:
David Steele <david@pgmasters.net> writes:
1) PL/SQL seems to be used in a few places where I believe PL/pgSQL is
meant. This was pre-existing but now seems like a good opportunity to
fix it, unless I am misunderstanding.PL/SQL is Oracle's function language, which PL/pgSQL is modeled on.
At least some of the mentions of PL/SQL are probably intentional,
so you'll have to look closely not just search-and-replace.
Ah, yes. That's what I get for just reading the patch and not looking at
the larger context.
Regards,
--
-David
david@pgmasters.net
I looked over the v2 patch. Parts of it seem like improvements but
other parts definitely don't. In particular, I thought you introduced
a great deal of confusion in 43.5.2 (Executing a Command with No Result).
The statement that you can write a non-result-returning SQL command as-is
is true in general, and ought not be confused with the question of whether
you can insert variable values into it. Also, starting with a spongy
definition of "utility command" and then contrasting with that does not
seem to me to add clarity.
I attach a v3 that I like better, although there's room to disagree
about that. I've always felt that the separation between 43.5.2 and
43.5.3 was rather artificial --- it's okay I guess for describing
how to handle command output, but we end up with considerable
duplication when it comes to describing how to insert values into a
command. It's tempting to try re-splitting it to separate optimizable
from non-optimizable statements; but maybe that'd just end with
different duplication.
regards, tom lane
Attachments:
v3-doc-plpgsql-variable-usage-cleanup.patchtext/x-diff; charset=us-ascii; name=v3-doc-plpgsql-variable-usage-cleanup.patchDownload
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 9242c54329..aa868b4191 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -894,7 +894,7 @@ SELECT <replaceable>expression</replaceable>
</synopsis>
to the main SQL engine. While forming the <command>SELECT</command> command,
any occurrences of <application>PL/pgSQL</application> variable names
- are replaced by parameters, as discussed in detail in
+ are replaced by query parameters, as discussed in detail in
<xref linkend="plpgsql-var-subst"/>.
This allows the query plan for the <command>SELECT</command> to
be prepared just once and then reused for subsequent
@@ -1004,20 +1004,32 @@ complex_array[n].realpart = 12.3;
</para>
<para>
- Any <application>PL/pgSQL</application> variable name appearing
- in the command text is treated as a parameter, and then the
+ In optimizable SQL commands (<command>INSERT</command>,
+ <command>UPDATE</command>, and <command>DELETE</command>),
+ any <application>PL/pgSQL</application> variable name appearing
+ in the command text is replaced by a query parameter, and then the
current value of the variable is provided as the parameter value
at run time. This is exactly like the processing described earlier
for expressions; for details see <xref linkend="plpgsql-var-subst"/>.
</para>
<para>
- When executing a SQL command in this way,
+ When executing an optimizable SQL command in this way,
<application>PL/pgSQL</application> may cache and re-use the execution
plan for the command, as discussed in
<xref linkend="plpgsql-plan-caching"/>.
</para>
+ <para>
+ Non-optimizable SQL commands (also called utility commands) are not
+ capable of accepting query parameters. So automatic substitution
+ of <application>PL/pgSQL</application> variables does not work in such
+ commands. To include non-constant text in a utility command executed
+ from <application>PL/pgSQL</application>, you must build the utility
+ command as a string and then <command>EXECUTE</command> it, as
+ discussed in <xref linkend="plpgsql-statements-executing-dyn"/>.
+ </para>
+
<para>
Sometimes it is useful to evaluate an expression or <command>SELECT</command>
query but discard the result, for example when calling a function
@@ -1095,11 +1107,11 @@ DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRIC
record/row fields.
<application>PL/pgSQL</application> variables will be
substituted into the rest of the query, and the plan is cached,
- just as described above for commands that do not return rows.
+ just as described above for optimizable commands that do not return rows.
This works for <command>SELECT</command>,
<command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> with
- <literal>RETURNING</literal>, and utility commands that return row-set
- results (such as <command>EXPLAIN</command>).
+ <literal>RETURNING</literal>, and certain utility commands
+ that return row sets, such as <command>EXPLAIN</command>.
Except for the <literal>INTO</literal> clause, the SQL command is the same
as it would be written outside <application>PL/pgSQL</application>.
</para>
@@ -2567,7 +2579,7 @@ $$ LANGUAGE plpgsql;
</para>
<para>
- <application>PL/pgSQL</application> variables are substituted into the query text,
+ <application>PL/pgSQL</application> variables are replaced by query parameters,
and the query plan is cached for possible re-use, as discussed in
detail in <xref linkend="plpgsql-var-subst"/> and
<xref linkend="plpgsql-plan-caching"/>.
@@ -4643,7 +4655,7 @@ CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
SQL statements and expressions within a <application>PL/pgSQL</application> function
can refer to variables and parameters of the function. Behind the scenes,
<application>PL/pgSQL</application> substitutes query parameters for such references.
- Parameters will only be substituted in places where a parameter or
+ Query parameters will only be substituted in places where a parameter or
column reference is syntactically allowed. As an extreme case, consider
this example of poor programming style:
<programlisting>
@@ -4657,13 +4669,6 @@ INSERT INTO foo (foo) VALUES (foo);
variable.
</para>
- <note>
- <para>
- <productname>PostgreSQL</productname> versions before 9.0 would try
- to substitute the variable in all three cases, leading to syntax errors.
- </para>
- </note>
-
<para>
Since the names of variables are syntactically no different from the names
of table columns, there can be ambiguity in statements that also refer to
@@ -5314,11 +5319,12 @@ HINT: Make sure the query returns the exact list of columns.
<listitem>
<para>
If a name used in a SQL command could be either a column name of a
- table or a reference to a variable of the function,
- <application>PL/SQL</application> treats it as a column name. This corresponds
- to <application>PL/pgSQL</application>'s
+ table used in the command or a reference to a variable of the function,
+ <application>PL/SQL</application> treats it as a column name.
+ By default, <application>PL/pgSQL</application> will treat it as a
+ variable, but you can specify
<literal>plpgsql.variable_conflict</literal> = <literal>use_column</literal>
- behavior, which is not the default,
+ to change this behavior to match <application>PL/SQL</application>,
as explained in <xref linkend="plpgsql-var-subst"/>.
It's often best to avoid such ambiguities in the first place,
but if you have to port a large amount of code that depends on
Hi
pá 12. 3. 2021 v 21:08 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
I looked over the v2 patch. Parts of it seem like improvements but
other parts definitely don't. In particular, I thought you introduced
a great deal of confusion in 43.5.2 (Executing a Command with No Result).
The statement that you can write a non-result-returning SQL command as-is
is true in general, and ought not be confused with the question of whether
you can insert variable values into it. Also, starting with a spongy
definition of "utility command" and then contrasting with that does not
seem to me to add clarity.I attach a v3 that I like better, although there's room to disagree
about that. I've always felt that the separation between 43.5.2 and
43.5.3 was rather artificial --- it's okay I guess for describing
how to handle command output, but we end up with considerable
duplication when it comes to describing how to insert values into a
command. It's tempting to try re-splitting it to separate optimizable
from non-optimizable statements; but maybe that'd just end with
different duplication.
I am not sure if people can understand the "optimizable command" term. More
common categories are DML, DDL and SELECT. Maybe it is easier to say. DDL
statements don't support parametrizations, and then the variables cannot be
used there.
Show quoted text
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
pá 12. 3. 2021 v 21:08 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
I attach a v3 that I like better, although there's room to disagree
about that.
I am not sure if people can understand the "optimizable command" term. More
common categories are DML, DDL and SELECT. Maybe it is easier to say. DDL
statements don't support parametrizations, and then the variables cannot be
used there.
Yeah, but DML/DDL is a pretty squishy separation as well, besides
which it'd mislead people for cases such as CREATE TABLE AS SELECT.
(Admittedly, I didn't mention that in my version either, but if you
think in terms of whether the optimizer will be applied then you
will draw the right conclusion.)
Maybe there's no way out but to specifically list the statement types
we can insert query parameters in.
regards, tom lane
pá 12. 3. 2021 v 21:36 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
pá 12. 3. 2021 v 21:08 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
I attach a v3 that I like better, although there's room to disagree
about that.I am not sure if people can understand the "optimizable command" term.
More
common categories are DML, DDL and SELECT. Maybe it is easier to say. DDL
statements don't support parametrizations, and then the variables cannotbe
used there.
Yeah, but DML/DDL is a pretty squishy separation as well, besides
which it'd mislead people for cases such as CREATE TABLE AS SELECT.
(Admittedly, I didn't mention that in my version either, but if you
think in terms of whether the optimizer will be applied then you
will draw the right conclusion.)
Can it be better to use word planner instead of optimizer? An optimization
is too common a word, and unfortunately a lot of people have no idea what
optimization in SQL means.
It can be pretty hard, because the people that have problems here don't
know what is a plan or what is an optimization.
Maybe there's no way out but to specifically list the statement types
we can insert query parameters in.
can be
Show quoted text
regards, tom lane
On Fri, Mar 12, 2021 at 1:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pavel Stehule <pavel.stehule@gmail.com> writes:
pá 12. 3. 2021 v 21:08 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
I attach a v3 that I like better, although there's room to disagree
about that.I am not sure if people can understand the "optimizable command" term.
More
common categories are DML, DDL and SELECT. Maybe it is easier to say. DDL
statements don't support parametrizations, and then the variables cannotbe
used there.
Yeah, but DML/DDL is a pretty squishy separation as well, besides
which it'd mislead people for cases such as CREATE TABLE AS SELECT.
(Admittedly, I didn't mention that in my version either, but if you
think in terms of whether the optimizer will be applied then you
will draw the right conclusion.)
Related to an earlier email though, "CREATE TABLE AS SELECT" gets optimized
but "COPY (SELECT) TO" doesn't...
DML/DDL has the merit of being chapters 5 and 6 in the documentation (with
7 being SELECT).
I do agree that the delineation of "returns records or not" is not ideal
here. SELECT, then INSERT/UPDATE/DELETE (due to their shared RETURNING
dynamic), then "DML commands", then "DMS exceptions" (these last two
ideally leveraging the conceptual work noted above). That said, I do not
think this is such a big issue as to warrant that much of a rewrite. But
in lieu of that, and based upon responses given on the mailing lists,
"utility commands" seems preferable to optimizable commands. Defining,
either by name or by behavior, what utility commands are is needed though,
ideally outside of this chapter. Then a paragraph in the "no result"
section should list explicitly those utility commands that are an
exception, since they have an attached SELECT statement that does get
optimized.
Maybe in Chapter 4, with some forward references, some of this can be
covered and the exceptions to the rule (like CREATE TABLE AS) can be
mentioned.
To address your point about "utility commands", lacking an external
definition to link to, I would change it to be "everything except
INSERT/UPDATE/DELETE, which are described below, as well as EXPLAIN and
SELECT which are described in the next section". From there I like my
proposed flow into INSERT/UPDATE/DELETE w/o RETURNING, then from there the
RETURNING pointing forward to these being SELECT-like in behavior.
Adding a note about using EXECUTE works for me.
Calling EXPLAIN a utility command seems incorrect given that it behaves
just like a query. If it quacks like a duck...
What other row set returning commands are you considering as being utility?
Maybe there's no way out but to specifically list the statement types
we can insert query parameters in.
In the following I'm confused as to why "column reference" is specified
since those are not substituted:
"Parameters will only be substituted in places where a parameter or
column reference is syntactically allowed."
I'm not married to my explicit calling out of identifiers not being
substitutable but that does tend to be what people try to do.
I'm good with the Pl/SQL wording proposal.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
I do agree that the delineation of "returns records or not" is not ideal
here. SELECT, then INSERT/UPDATE/DELETE (due to their shared RETURNING
dynamic), then "DML commands", then "DMS exceptions" (these last two
ideally leveraging the conceptual work noted above). That said, I do not
think this is such a big issue as to warrant that much of a rewrite.
I took a stab at doing that, just to see what it might look like.
I thought it comes out pretty well, really -- see what you think.
(This still uses the terminology "optimizable statement", but I'm open
to replacing that with something else.)
In the following I'm confused as to why "column reference" is specified
since those are not substituted:
"Parameters will only be substituted in places where a parameter or
column reference is syntactically allowed."
The meaning of "column reference" there is, I think, a reference to
a column of a table being read by a query. In the counterexample
of "INSERT INTO mytable (col) ...", "col" cannot be replaced by a
data value. But in "INSERT INTO mytable (col) SELECT foo FROM bar",
"foo" is a candidate for replacement, even though it's likely meant
as a reference to bar.foo.
I'm not married to my explicit calling out of identifiers not being
substitutable but that does tend to be what people try to do.
The problem I had with it was that it didn't help clarify this
distinction. I'm certainly open to changes that do clarify that.
regards, tom lane
Attachments:
v4-doc-plpgsql-variable-usage-cleanup.patchtext/x-diff; charset=us-ascii; name=v4-doc-plpgsql-variable-usage-cleanup.patchDownload
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 9242c54329..15117c78cb 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -894,7 +894,7 @@ SELECT <replaceable>expression</replaceable>
</synopsis>
to the main SQL engine. While forming the <command>SELECT</command> command,
any occurrences of <application>PL/pgSQL</application> variable names
- are replaced by parameters, as discussed in detail in
+ are replaced by query parameters, as discussed in detail in
<xref linkend="plpgsql-var-subst"/>.
This allows the query plan for the <command>SELECT</command> to
be prepared just once and then reused for subsequent
@@ -946,8 +946,7 @@ IF count(*) > 0 FROM my_table THEN ...
<application>PL/pgSQL</application>.
Anything not recognized as one of these statement types is presumed
to be an SQL command and is sent to the main database engine to execute,
- as described in <xref linkend="plpgsql-statements-sql-noresult"/>
- and <xref linkend="plpgsql-statements-sql-onerow"/>.
+ as described in <xref linkend="plpgsql-statements-general-sql"/>.
</para>
<sect2 id="plpgsql-statements-assignment">
@@ -993,31 +992,78 @@ complex_array[n].realpart = 12.3;
</para>
</sect2>
- <sect2 id="plpgsql-statements-sql-noresult">
- <title>Executing a Command with No Result</title>
+ <sect2 id="plpgsql-statements-general-sql">
+ <title>Executing SQL Commands</title>
<para>
- For any SQL command that does not return rows, for example
- <command>INSERT</command> without a <literal>RETURNING</literal> clause, you can
- execute the command within a <application>PL/pgSQL</application> function
- just by writing the command.
+ In general, any SQL command that does not return rows can be executed
+ within a <application>PL/pgSQL</application> function just by writing
+ the command. For example, you could create and fill a table by writing
+<programlisting>
+CREATE TABLE mytable (id int primary key, data text);
+INSERT INTO mytable VALUES (1,'one'), (2,'two');
+</programlisting>
+ </para>
+
+ <para>
+ If the command does return rows (for example <command>SELECT</command>,
+ or <command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>
+ with <literal>RETURNING</literal>), there are two ways to proceed.
+ When the command will return at most one row, or you only care about
+ the first row of output, write the command as usual but add
+ an <literal>INTO</literal> clause to capture the output, as described
+ in <xref linkend="plpgsql-statements-sql-onerow"/>.
+ To process all of the output rows, write the command as the data
+ source for a <command>FOR</command> loop, as described in
+ <xref linkend="plpgsql-records-iterating"/>.
</para>
<para>
- Any <application>PL/pgSQL</application> variable name appearing
- in the command text is treated as a parameter, and then the
+ Usually it is not sufficient to just execute statically-defined SQL
+ commands. Typically you'll want a command to use varying data values,
+ or even to vary in more fundamental ways such as by using different
+ table names at different times. Again, there are two ways to proceed,
+ depending on the particular command you need to execute.
+ </para>
+
+ <para>
+ <application>PL/pgSQL</application> variable values can be
+ automatically inserted into optimizable SQL commands, which
+ are <command>SELECT</command>, <command>INSERT</command>,
+ <command>UPDATE</command>, <command>DELETE</command>, and certain
+ utility commands that incorporate one of these, such
+ as <command>EXPLAIN</command> and <command>CREATE TABLE ... AS
+ SELECT</command>. In these commands,
+ any <application>PL/pgSQL</application> variable name appearing
+ in the command text is replaced by a query parameter, and then the
current value of the variable is provided as the parameter value
at run time. This is exactly like the processing described earlier
for expressions; for details see <xref linkend="plpgsql-var-subst"/>.
</para>
<para>
- When executing a SQL command in this way,
+ When executing an optimizable SQL command in this way,
<application>PL/pgSQL</application> may cache and re-use the execution
plan for the command, as discussed in
<xref linkend="plpgsql-plan-caching"/>.
</para>
+ <para>
+ Non-optimizable SQL commands (also called utility commands) are not
+ capable of accepting query parameters. So automatic substitution
+ of <application>PL/pgSQL</application> variables does not work in such
+ commands. To include non-constant text in a utility command executed
+ from <application>PL/pgSQL</application>, you must build the utility
+ command as a string and then <command>EXECUTE</command> it, as
+ discussed in <xref linkend="plpgsql-statements-executing-dyn"/>.
+ </para>
+
+ <para>
+ <command>EXECUTE</command> must also be used if you want to modify
+ the command in some other way than supplying a data value, for example
+ by changing a table name.
+ </para>
+
<para>
Sometimes it is useful to evaluate an expression or <command>SELECT</command>
query but discard the result, for example when calling a function
@@ -1037,7 +1083,7 @@ PERFORM <replaceable>query</replaceable>;
place the query in parentheses. (In this case, the query can only
return one row.)
<application>PL/pgSQL</application> variables will be
- substituted into the query just as for commands that return no result,
+ substituted into the query just as described above,
and the plan is cached in the same way. Also, the special variable
<literal>FOUND</literal> is set to true if the query produced at
least one row, or false if it produced no rows (see
@@ -1065,7 +1111,7 @@ PERFORM create_mv('cs_session_page_requests_mv', my_query);
</sect2>
<sect2 id="plpgsql-statements-sql-onerow">
- <title>Executing a Query with a Single-Row Result</title>
+ <title>Executing a Command with a Single-Row Result</title>
<indexterm zone="plpgsql-statements-sql-onerow">
<primary>SELECT INTO</primary>
@@ -1094,12 +1140,13 @@ DELETE ... RETURNING <replaceable>expressions</replaceable> INTO <optional>STRIC
variable, or a comma-separated list of simple variables and
record/row fields.
<application>PL/pgSQL</application> variables will be
- substituted into the rest of the query, and the plan is cached,
- just as described above for commands that do not return rows.
+ substituted into the rest of the query (that is, everything but the
+ <literal>INTO</literal> clause) just as described above,
+ and the plan is cached in the same way.
This works for <command>SELECT</command>,
<command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command> with
- <literal>RETURNING</literal>, and utility commands that return row-set
- results (such as <command>EXPLAIN</command>).
+ <literal>RETURNING</literal>, and certain utility commands
+ that return row sets, such as <command>EXPLAIN</command>.
Except for the <literal>INTO</literal> clause, the SQL command is the same
as it would be written outside <application>PL/pgSQL</application>.
</para>
@@ -1220,11 +1267,6 @@ CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
</para>
</note>
- <para>
- To handle cases where you need to process multiple result rows
- from a SQL query, see <xref linkend="plpgsql-records-iterating"/>.
- </para>
-
</sect2>
<sect2 id="plpgsql-statements-executing-dyn">
@@ -2567,7 +2609,7 @@ $$ LANGUAGE plpgsql;
</para>
<para>
- <application>PL/pgSQL</application> variables are substituted into the query text,
+ <application>PL/pgSQL</application> variables are replaced by query parameters,
and the query plan is cached for possible re-use, as discussed in
detail in <xref linkend="plpgsql-var-subst"/> and
<xref linkend="plpgsql-plan-caching"/>.
@@ -4643,7 +4685,7 @@ CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();
SQL statements and expressions within a <application>PL/pgSQL</application> function
can refer to variables and parameters of the function. Behind the scenes,
<application>PL/pgSQL</application> substitutes query parameters for such references.
- Parameters will only be substituted in places where a parameter or
+ Query parameters will only be substituted in places where a parameter or
column reference is syntactically allowed. As an extreme case, consider
this example of poor programming style:
<programlisting>
@@ -4657,13 +4699,6 @@ INSERT INTO foo (foo) VALUES (foo);
variable.
</para>
- <note>
- <para>
- <productname>PostgreSQL</productname> versions before 9.0 would try
- to substitute the variable in all three cases, leading to syntax errors.
- </para>
- </note>
-
<para>
Since the names of variables are syntactically no different from the names
of table columns, there can be ambiguity in statements that also refer to
@@ -5314,11 +5349,12 @@ HINT: Make sure the query returns the exact list of columns.
<listitem>
<para>
If a name used in a SQL command could be either a column name of a
- table or a reference to a variable of the function,
- <application>PL/SQL</application> treats it as a column name. This corresponds
- to <application>PL/pgSQL</application>'s
+ table used in the command or a reference to a variable of the function,
+ <application>PL/SQL</application> treats it as a column name.
+ By default, <application>PL/pgSQL</application> will treat it as a
+ variable, but you can specify
<literal>plpgsql.variable_conflict</literal> = <literal>use_column</literal>
- behavior, which is not the default,
+ to change this behavior to match <application>PL/SQL</application>,
as explained in <xref linkend="plpgsql-var-subst"/>.
It's often best to avoid such ambiguities in the first place,
but if you have to port a large amount of code that depends on
I wrote:
I took a stab at doing that, just to see what it might look like.
I thought it comes out pretty well, really -- see what you think.
Hearing nothing further, I pushed that after another round of
copy-editing. There's still plenty of time to revise it if
anybody has further comments.
regards, tom lane