"David G. Johnston" <[email protected]> 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
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
