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
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
