diff options
Diffstat (limited to 'doc/src/sgml/html/plpgsql-implementation.html')
| -rw-r--r-- | doc/src/sgml/html/plpgsql-implementation.html | 276 |
1 files changed, 276 insertions, 0 deletions
diff --git a/doc/src/sgml/html/plpgsql-implementation.html b/doc/src/sgml/html/plpgsql-implementation.html new file mode 100644 index 0000000..cd59880 --- /dev/null +++ b/doc/src/sgml/html/plpgsql-implementation.html @@ -0,0 +1,276 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.11. PL/pgSQL under the Hood</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql-trigger.html" title="43.10. Trigger Functions" /><link rel="next" href="plpgsql-development-tips.html" title="43.12. Tips for Developing in PL/pgSQL" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">43.11. <span class="application">PL/pgSQL</span> under the Hood</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-trigger.html" title="43.10. Trigger Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-development-tips.html" title="43.12. Tips for Developing in PL/pgSQL">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-IMPLEMENTATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.11. <span class="application">PL/pgSQL</span> under the Hood <a href="#PLPGSQL-IMPLEMENTATION" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-implementation.html#PLPGSQL-VAR-SUBST">43.11.1. Variable Substitution</a></span></dt><dt><span class="sect2"><a href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING">43.11.2. Plan Caching</a></span></dt></dl></div><p> + This section discusses some implementation details that are + frequently important for <span class="application">PL/pgSQL</span> users to know. + </p><div class="sect2" id="PLPGSQL-VAR-SUBST"><div class="titlepage"><div><div><h3 class="title">43.11.1. Variable Substitution <a href="#PLPGSQL-VAR-SUBST" class="id_link">#</a></h3></div></div></div><p> + SQL statements and expressions within a <span class="application">PL/pgSQL</span> function + can refer to variables and parameters of the function. Behind the scenes, + <span class="application">PL/pgSQL</span> substitutes query parameters for such references. + Query parameters will only be substituted in places where they are + syntactically permissible. As an extreme case, consider + this example of poor programming style: +</p><pre class="programlisting"> +INSERT INTO foo (foo) VALUES (foo(foo)); +</pre><p> + The first occurrence of <code class="literal">foo</code> must syntactically be a table + name, so it will not be substituted, even if the function has a variable + named <code class="literal">foo</code>. The second occurrence must be the name of a + column of that table, so it will not be substituted either. Likewise + the third occurrence must be a function name, so it also will not be + substituted for. Only the last occurrence is a candidate to be a + reference to a variable of the <span class="application">PL/pgSQL</span> + function. + </p><p> + Another way to understand this is that variable substitution can only + insert data values into an SQL command; it cannot dynamically change which + database objects are referenced by the command. (If you want to do + that, you must build a command string dynamically, as explained in + <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="43.5.4. Executing Dynamic Commands">Section 43.5.4</a>.) + </p><p> + 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 + tables: is a given name meant to refer to a table column, or a variable? + Let's change the previous example to +</p><pre class="programlisting"> +INSERT INTO dest (col) SELECT foo + bar FROM src; +</pre><p> + Here, <code class="literal">dest</code> and <code class="literal">src</code> must be table names, and + <code class="literal">col</code> must be a column of <code class="literal">dest</code>, but <code class="literal">foo</code> + and <code class="literal">bar</code> might reasonably be either variables of the function + or columns of <code class="literal">src</code>. + </p><p> + By default, <span class="application">PL/pgSQL</span> will report an error if a name + in an SQL statement could refer to either a variable or a table column. + You can fix such a problem by renaming the variable or column, + or by qualifying the ambiguous reference, or by telling + <span class="application">PL/pgSQL</span> which interpretation to prefer. + </p><p> + The simplest solution is to rename the variable or column. + A common coding rule is to use a + different naming convention for <span class="application">PL/pgSQL</span> + variables than you use for column names. For example, + if you consistently name function variables + <code class="literal">v_<em class="replaceable"><code>something</code></em></code> while none of your + column names start with <code class="literal">v_</code>, no conflicts will occur. + </p><p> + Alternatively you can qualify ambiguous references to make them clear. + In the above example, <code class="literal">src.foo</code> would be an unambiguous reference + to the table column. To create an unambiguous reference to a variable, + declare it in a labeled block and use the block's label + (see <a class="xref" href="plpgsql-structure.html" title="43.2. Structure of PL/pgSQL">Section 43.2</a>). For example, +</p><pre class="programlisting"> +<<block>> +DECLARE + foo int; +BEGIN + foo := ...; + INSERT INTO dest (col) SELECT block.foo + bar FROM src; +</pre><p> + Here <code class="literal">block.foo</code> means the variable even if there is a column + <code class="literal">foo</code> in <code class="literal">src</code>. Function parameters, as well as + special variables such as <code class="literal">FOUND</code>, can be qualified by the + function's name, because they are implicitly declared in an outer block + labeled with the function's name. + </p><p> + Sometimes it is impractical to fix all the ambiguous references in a + large body of <span class="application">PL/pgSQL</span> code. In such cases you can + specify that <span class="application">PL/pgSQL</span> should resolve ambiguous references + as the variable (which is compatible with <span class="application">PL/pgSQL</span>'s + behavior before <span class="productname">PostgreSQL</span> 9.0), or as the + table column (which is compatible with some other systems such as + <span class="productname">Oracle</span>). + </p><a id="id-1.8.8.13.3.9" class="indexterm"></a><p> + To change this behavior on a system-wide basis, set the configuration + parameter <code class="literal">plpgsql.variable_conflict</code> to one of + <code class="literal">error</code>, <code class="literal">use_variable</code>, or + <code class="literal">use_column</code> (where <code class="literal">error</code> is the factory default). + This parameter affects subsequent compilations + of statements in <span class="application">PL/pgSQL</span> functions, but not statements + already compiled in the current session. + Because changing this setting + can cause unexpected changes in the behavior of <span class="application">PL/pgSQL</span> + functions, it can only be changed by a superuser. + </p><p> + You can also set the behavior on a function-by-function basis, by + inserting one of these special commands at the start of the function + text: +</p><pre class="programlisting"> +#variable_conflict error +#variable_conflict use_variable +#variable_conflict use_column +</pre><p> + These commands affect only the function they are written in, and override + the setting of <code class="literal">plpgsql.variable_conflict</code>. An example is +</p><pre class="programlisting"> +CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ + #variable_conflict use_variable + DECLARE + curtime timestamp := now(); + BEGIN + UPDATE users SET last_modified = curtime, comment = comment + WHERE users.id = id; + END; +$$ LANGUAGE plpgsql; +</pre><p> + In the <code class="literal">UPDATE</code> command, <code class="literal">curtime</code>, <code class="literal">comment</code>, + and <code class="literal">id</code> will refer to the function's variable and parameters + whether or not <code class="literal">users</code> has columns of those names. Notice + that we had to qualify the reference to <code class="literal">users.id</code> in the + <code class="literal">WHERE</code> clause to make it refer to the table column. + But we did not have to qualify the reference to <code class="literal">comment</code> + as a target in the <code class="literal">UPDATE</code> list, because syntactically + that must be a column of <code class="literal">users</code>. We could write the same + function without depending on the <code class="literal">variable_conflict</code> setting + in this way: +</p><pre class="programlisting"> +CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ + <<fn>> + DECLARE + curtime timestamp := now(); + BEGIN + UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment + WHERE users.id = stamp_user.id; + END; +$$ LANGUAGE plpgsql; +</pre><p> + </p><p> + Variable substitution does not happen in a command string given + to <code class="command">EXECUTE</code> or one of its variants. If you need to + insert a varying value into such a command, do so as part of + constructing the string value, or use <code class="literal">USING</code>, as illustrated in + <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="43.5.4. Executing Dynamic Commands">Section 43.5.4</a>. + </p><p> + Variable substitution currently works only in <code class="command">SELECT</code>, + <code class="command">INSERT</code>, <code class="command">UPDATE</code>, + <code class="command">DELETE</code>, and commands containing one of + these (such as <code class="command">EXPLAIN</code> and <code class="command">CREATE TABLE + ... AS SELECT</code>), + 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 <code class="command">EXECUTE</code> it. + </p></div><div class="sect2" id="PLPGSQL-PLAN-CACHING"><div class="titlepage"><div><div><h3 class="title">43.11.2. Plan Caching <a href="#PLPGSQL-PLAN-CACHING" class="id_link">#</a></h3></div></div></div><p> + The <span class="application">PL/pgSQL</span> interpreter parses the function's source + text and produces an internal binary instruction tree the first time the + function is called (within each session). The instruction tree + fully translates the + <span class="application">PL/pgSQL</span> statement structure, but individual + <acronym class="acronym">SQL</acronym> expressions and <acronym class="acronym">SQL</acronym> commands + used in the function are not translated immediately. + </p><p> + <a id="id-1.8.8.13.4.3.1" class="indexterm"></a> + As each expression and <acronym class="acronym">SQL</acronym> command is first + executed in the function, the <span class="application">PL/pgSQL</span> interpreter + parses and analyzes the command to create a prepared statement, + using the <acronym class="acronym">SPI</acronym> manager's + <code class="function">SPI_prepare</code> function. + Subsequent visits to that expression or command + reuse the prepared statement. Thus, a function with conditional code + paths that are seldom visited will never incur the overhead of + analyzing those commands that are never executed within the current + session. A disadvantage is that errors + in a specific expression or command cannot be detected until that + part of the function is reached in execution. (Trivial syntax + errors will be detected during the initial parsing pass, but + anything deeper will not be detected until execution.) + </p><p> + <span class="application">PL/pgSQL</span> (or more precisely, the SPI manager) can + furthermore attempt to cache the execution plan associated with any + particular prepared statement. If a cached plan is not used, then + a fresh execution plan is generated on each visit to the statement, + and the current parameter values (that is, <span class="application">PL/pgSQL</span> + variable values) can be used to optimize the selected plan. If the + statement has no parameters, or is executed many times, the SPI manager + will consider creating a <em class="firstterm">generic</em> plan that is not dependent + on specific parameter values, and caching that for re-use. Typically + this will happen only if the execution plan is not very sensitive to + the values of the <span class="application">PL/pgSQL</span> variables referenced in it. + If it is, generating a plan each time is a net win. See <a class="xref" href="sql-prepare.html" title="PREPARE"><span class="refentrytitle">PREPARE</span></a> for more information about the behavior of + prepared statements. + </p><p> + Because <span class="application">PL/pgSQL</span> saves prepared statements + and sometimes execution plans in this way, + SQL commands that appear directly in a + <span class="application">PL/pgSQL</span> function must refer to the + same tables and columns on every execution; that is, you cannot use + a parameter as the name of a table or column in an SQL command. To get + around this restriction, you can construct dynamic commands using + the <span class="application">PL/pgSQL</span> <code class="command">EXECUTE</code> + statement — at the price of performing new parse analysis and + constructing a new execution plan on every execution. + </p><p> + The mutable nature of record variables presents another problem in this + connection. When fields of a record variable are used in + expressions or statements, the data types of the fields must not + change from one call of the function to the next, since each + expression will be analyzed using the data type that is present + when the expression is first reached. <code class="command">EXECUTE</code> can be + used to get around this problem when necessary. + </p><p> + If the same function is used as a trigger for more than one table, + <span class="application">PL/pgSQL</span> prepares and caches statements + independently for each such table — that is, there is a cache + for each trigger function and table combination, not just for each + function. This alleviates some of the problems with varying + data types; for instance, a trigger function will be able to work + successfully with a column named <code class="literal">key</code> even if it happens + to have different types in different tables. + </p><p> + Likewise, functions having polymorphic argument types have a separate + statement cache for each combination of actual argument types they have + been invoked for, so that data type differences do not cause unexpected + failures. + </p><p> + Statement caching can sometimes have surprising effects on the + interpretation of time-sensitive values. For example there + is a difference between what these two functions do: + +</p><pre class="programlisting"> +CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$ + BEGIN + INSERT INTO logtable VALUES (logtxt, 'now'); + END; +$$ LANGUAGE plpgsql; +</pre><p> + + and: + +</p><pre class="programlisting"> +CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$ + DECLARE + curtime timestamp; + BEGIN + curtime := 'now'; + INSERT INTO logtable VALUES (logtxt, curtime); + END; +$$ LANGUAGE plpgsql; +</pre><p> + </p><p> + In the case of <code class="function">logfunc1</code>, the + <span class="productname">PostgreSQL</span> main parser knows when + analyzing the <code class="command">INSERT</code> that the + string <code class="literal">'now'</code> should be interpreted as + <code class="type">timestamp</code>, because the target column of + <code class="classname">logtable</code> is of that type. Thus, + <code class="literal">'now'</code> will be converted to a <code class="type">timestamp</code> + constant when the + <code class="command">INSERT</code> is analyzed, and then used in all + invocations of <code class="function">logfunc1</code> during the lifetime + of the session. Needless to say, this isn't what the programmer + wanted. A better idea is to use the <code class="literal">now()</code> or + <code class="literal">current_timestamp</code> function. + </p><p> + In the case of <code class="function">logfunc2</code>, the + <span class="productname">PostgreSQL</span> main parser does not know + what type <code class="literal">'now'</code> should become and therefore + it returns a data value of type <code class="type">text</code> containing the string + <code class="literal">now</code>. During the ensuing assignment + to the local variable <code class="varname">curtime</code>, the + <span class="application">PL/pgSQL</span> interpreter casts this + string to the <code class="type">timestamp</code> type by calling the + <code class="function">textout</code> and <code class="function">timestamp_in</code> + functions for the conversion. So, the computed time stamp is updated + on each execution as the programmer expects. Even though this + happens to work as expected, it's not terribly efficient, so + use of the <code class="literal">now()</code> function would still be a better idea. + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-trigger.html" title="43.10. Trigger Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-development-tips.html" title="43.12. Tips for Developing in PL/pgSQL">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.10. Trigger Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.12. Tips for Developing in <span class="application">PL/pgSQL</span></td></tr></table></div></body></html>
\ No newline at end of file |