codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3 This section discusses some implementation details that are4 frequently important for <span class="application">PL/pgSQL</span> users to know.5 </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>6 SQL statements and expressions within a <span class="application">PL/pgSQL</span> function7 can refer to variables and parameters of the function. Behind the scenes,8 <span class="application">PL/pgSQL</span> substitutes query parameters for such references.9 Query parameters will only be substituted in places where they are10 syntactically permissible. As an extreme case, consider11 this example of poor programming style:12</p><pre class="programlisting">13INSERT INTO foo (foo) VALUES (foo(foo));14</pre><p>15 The first occurrence of <code class="literal">foo</code> must syntactically be a table16 name, so it will not be substituted, even if the function has a variable17 named <code class="literal">foo</code>. The second occurrence must be the name of a18 column of that table, so it will not be substituted either. Likewise19 the third occurrence must be a function name, so it also will not be20 substituted for. Only the last occurrence is a candidate to be a21 reference to a variable of the <span class="application">PL/pgSQL</span>22 function.23 </p><p>24 Another way to understand this is that variable substitution can only25 insert data values into an SQL command; it cannot dynamically change which26 database objects are referenced by the command. (If you want to do27 that, you must build a command string dynamically, as explained in28 <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="43.5.4. Executing Dynamic Commands">Section 43.5.4</a>.)29 </p><p>30 Since the names of variables are syntactically no different from the names31 of table columns, there can be ambiguity in statements that also refer to32 tables: is a given name meant to refer to a table column, or a variable?33 Let's change the previous example to34</p><pre class="programlisting">35INSERT INTO dest (col) SELECT foo + bar FROM src;36</pre><p>37 Here, <code class="literal">dest</code> and <code class="literal">src</code> must be table names, and38 <code class="literal">col</code> must be a column of <code class="literal">dest</code>, but <code class="literal">foo</code>39 and <code class="literal">bar</code> might reasonably be either variables of the function40 or columns of <code class="literal">src</code>.41 </p><p>42 By default, <span class="application">PL/pgSQL</span> will report an error if a name43 in an SQL statement could refer to either a variable or a table column.44 You can fix such a problem by renaming the variable or column,45 or by qualifying the ambiguous reference, or by telling46 <span class="application">PL/pgSQL</span> which interpretation to prefer.47 </p><p>48 The simplest solution is to rename the variable or column.49 A common coding rule is to use a50 different naming convention for <span class="application">PL/pgSQL</span>51 variables than you use for column names. For example,52 if you consistently name function variables53 <code class="literal">v_<em class="replaceable"><code>something</code></em></code> while none of your54 column names start with <code class="literal">v_</code>, no conflicts will occur.55 </p><p>56 Alternatively you can qualify ambiguous references to make them clear.57 In the above example, <code class="literal">src.foo</code> would be an unambiguous reference58 to the table column. To create an unambiguous reference to a variable,59 declare it in a labeled block and use the block's label60 (see <a class="xref" href="plpgsql-structure.html" title="43.2. Structure of PL/pgSQL">Section 43.2</a>). For example,61</p><pre class="programlisting">62<<block>>63DECLARE64 foo int;65BEGIN66 foo := ...;67 INSERT INTO dest (col) SELECT block.foo + bar FROM src;68</pre><p>69 Here <code class="literal">block.foo</code> means the variable even if there is a column70 <code class="literal">foo</code> in <code class="literal">src</code>. Function parameters, as well as71 special variables such as <code class="literal">FOUND</code>, can be qualified by the72 function's name, because they are implicitly declared in an outer block73 labeled with the function's name.74 </p><p>75 Sometimes it is impractical to fix all the ambiguous references in a76 large body of <span class="application">PL/pgSQL</span> code. In such cases you can77 specify that <span class="application">PL/pgSQL</span> should resolve ambiguous references78 as the variable (which is compatible with <span class="application">PL/pgSQL</span>'s79 behavior before <span class="productname">PostgreSQL</span> 9.0), or as the80 table column (which is compatible with some other systems such as81 <span class="productname">Oracle</span>).82 </p><a id="id-1.8.8.13.3.9" class="indexterm"></a><p>83 To change this behavior on a system-wide basis, set the configuration84 parameter <code class="literal">plpgsql.variable_conflict</code> to one of85 <code class="literal">error</code>, <code class="literal">use_variable</code>, or86 <code class="literal">use_column</code> (where <code class="literal">error</code> is the factory default).87 This parameter affects subsequent compilations88 of statements in <span class="application">PL/pgSQL</span> functions, but not statements89 already compiled in the current session.90 Because changing this setting91 can cause unexpected changes in the behavior of <span class="application">PL/pgSQL</span>92 functions, it can only be changed by a superuser.93 </p><p>94 You can also set the behavior on a function-by-function basis, by95 inserting one of these special commands at the start of the function96 text:97</p><pre class="programlisting">98#variable_conflict error99#variable_conflict use_variable100#variable_conflict use_column101</pre><p>102 These commands affect only the function they are written in, and override103 the setting of <code class="literal">plpgsql.variable_conflict</code>. An example is104</p><pre class="programlisting">105CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$106 #variable_conflict use_variable107 DECLARE108 curtime timestamp := now();109 BEGIN110 UPDATE users SET last_modified = curtime, comment = comment111 WHERE users.id = id;112 END;113$$ LANGUAGE plpgsql;114</pre><p>115 In the <code class="literal">UPDATE</code> command, <code class="literal">curtime</code>, <code class="literal">comment</code>,116 and <code class="literal">id</code> will refer to the function's variable and parameters117 whether or not <code class="literal">users</code> has columns of those names. Notice118 that we had to qualify the reference to <code class="literal">users.id</code> in the119 <code class="literal">WHERE</code> clause to make it refer to the table column.120 But we did not have to qualify the reference to <code class="literal">comment</code>121 as a target in the <code class="literal">UPDATE</code> list, because syntactically122 that must be a column of <code class="literal">users</code>. We could write the same123 function without depending on the <code class="literal">variable_conflict</code> setting124 in this way:125</p><pre class="programlisting">126CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$127 <<fn>>128 DECLARE129 curtime timestamp := now();130 BEGIN131 UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment132 WHERE users.id = stamp_user.id;133 END;134$$ LANGUAGE plpgsql;135</pre><p>136 </p><p>137 Variable substitution does not happen in a command string given138 to <code class="command">EXECUTE</code> or one of its variants. If you need to139 insert a varying value into such a command, do so as part of140 constructing the string value, or use <code class="literal">USING</code>, as illustrated in141 <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="43.5.4. Executing Dynamic Commands">Section 43.5.4</a>.142 </p><p>143 Variable substitution currently works only in <code class="command">SELECT</code>,144 <code class="command">INSERT</code>, <code class="command">UPDATE</code>,145 <code class="command">DELETE</code>, and commands containing one of146 these (such as <code class="command">EXPLAIN</code> and <code class="command">CREATE TABLE147 ... AS SELECT</code>),148 because the main SQL engine allows query parameters only in these149 commands. To use a non-constant name or value in other statement150 types (generically called utility statements), you must construct151 the utility statement as a string and <code class="command">EXECUTE</code> it.152 </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>153 The <span class="application">PL/pgSQL</span> interpreter parses the function's source154 text and produces an internal binary instruction tree the first time the155 function is called (within each session). The instruction tree156 fully translates the157 <span class="application">PL/pgSQL</span> statement structure, but individual158 <acronym class="acronym">SQL</acronym> expressions and <acronym class="acronym">SQL</acronym> commands159 used in the function are not translated immediately.160 </p><p>161 <a id="id-1.8.8.13.4.3.1" class="indexterm"></a>162 As each expression and <acronym class="acronym">SQL</acronym> command is first163 executed in the function, the <span class="application">PL/pgSQL</span> interpreter164 parses and analyzes the command to create a prepared statement,165 using the <acronym class="acronym">SPI</acronym> manager's166 <code class="function">SPI_prepare</code> function.167 Subsequent visits to that expression or command168 reuse the prepared statement. Thus, a function with conditional code169 paths that are seldom visited will never incur the overhead of170 analyzing those commands that are never executed within the current171 session. A disadvantage is that errors172 in a specific expression or command cannot be detected until that173 part of the function is reached in execution. (Trivial syntax174 errors will be detected during the initial parsing pass, but175 anything deeper will not be detected until execution.)176 </p><p>177 <span class="application">PL/pgSQL</span> (or more precisely, the SPI manager) can178 furthermore attempt to cache the execution plan associated with any179 particular prepared statement. If a cached plan is not used, then180 a fresh execution plan is generated on each visit to the statement,181 and the current parameter values (that is, <span class="application">PL/pgSQL</span>182 variable values) can be used to optimize the selected plan. If the183 statement has no parameters, or is executed many times, the SPI manager184 will consider creating a <em class="firstterm">generic</em> plan that is not dependent185 on specific parameter values, and caching that for re-use. Typically186 this will happen only if the execution plan is not very sensitive to187 the values of the <span class="application">PL/pgSQL</span> variables referenced in it.188 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 of189 prepared statements.190 </p><p>191 Because <span class="application">PL/pgSQL</span> saves prepared statements192 and sometimes execution plans in this way,193 SQL commands that appear directly in a194 <span class="application">PL/pgSQL</span> function must refer to the195 same tables and columns on every execution; that is, you cannot use196 a parameter as the name of a table or column in an SQL command. To get197 around this restriction, you can construct dynamic commands using198 the <span class="application">PL/pgSQL</span> <code class="command">EXECUTE</code>199 statement — at the price of performing new parse analysis and200 constructing a new execution plan on every execution.201 </p><p>202 The mutable nature of record variables presents another problem in this203 connection. When fields of a record variable are used in204 expressions or statements, the data types of the fields must not205 change from one call of the function to the next, since each206 expression will be analyzed using the data type that is present207 when the expression is first reached. <code class="command">EXECUTE</code> can be208 used to get around this problem when necessary.209 </p><p>210 If the same function is used as a trigger for more than one table,211 <span class="application">PL/pgSQL</span> prepares and caches statements212 independently for each such table — that is, there is a cache213 for each trigger function and table combination, not just for each214 function. This alleviates some of the problems with varying215 data types; for instance, a trigger function will be able to work216 successfully with a column named <code class="literal">key</code> even if it happens217 to have different types in different tables.218 </p><p>219 Likewise, functions having polymorphic argument types have a separate220 statement cache for each combination of actual argument types they have221 been invoked for, so that data type differences do not cause unexpected222 failures.223 </p><p>224 Statement caching can sometimes have surprising effects on the225 interpretation of time-sensitive values. For example there226 is a difference between what these two functions do:227 228</p><pre class="programlisting">229CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$230 BEGIN231 INSERT INTO logtable VALUES (logtxt, 'now');232 END;233$$ LANGUAGE plpgsql;234</pre><p>235 236 and:237 238</p><pre class="programlisting">239CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$240 DECLARE241 curtime timestamp;242 BEGIN243 curtime := 'now';244 INSERT INTO logtable VALUES (logtxt, curtime);245 END;246$$ LANGUAGE plpgsql;247</pre><p>248 </p><p>249 In the case of <code class="function">logfunc1</code>, the250 <span class="productname">PostgreSQL</span> main parser knows when251 analyzing the <code class="command">INSERT</code> that the252 string <code class="literal">'now'</code> should be interpreted as253 <code class="type">timestamp</code>, because the target column of254 <code class="classname">logtable</code> is of that type. Thus,255 <code class="literal">'now'</code> will be converted to a <code class="type">timestamp</code>256 constant when the257 <code class="command">INSERT</code> is analyzed, and then used in all258 invocations of <code class="function">logfunc1</code> during the lifetime259 of the session. Needless to say, this isn't what the programmer260 wanted. A better idea is to use the <code class="literal">now()</code> or261 <code class="literal">current_timestamp</code> function.262 </p><p>263 In the case of <code class="function">logfunc2</code>, the264 <span class="productname">PostgreSQL</span> main parser does not know265 what type <code class="literal">'now'</code> should become and therefore266 it returns a data value of type <code class="type">text</code> containing the string267 <code class="literal">now</code>. During the ensuing assignment268 to the local variable <code class="varname">curtime</code>, the269 <span class="application">PL/pgSQL</span> interpreter casts this270 string to the <code class="type">timestamp</code> type by calling the271 <code class="function">textout</code> and <code class="function">timestamp_in</code>272 functions for the conversion. So, the computed time stamp is updated273 on each execution as the programmer expects. Even though this274 happens to work as expected, it's not terribly efficient, so275 use of the <code class="literal">now()</code> function would still be a better idea.276 </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.3 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>