"David G. Johnston" <david.g.johns...@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
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