begriffs open source - ai-pg/blob - full-docs/src/sgml/html/plpgsql-implementation.html

   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>41.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="41.10. Trigger Functions" /><link rel="next" href="plpgsql-development-tips.html" title="41.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">41.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="41.10. Trigger Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 41. <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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-development-tips.html" title="41.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">41.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">41.11.1. Variable Substitution</a></span></dt><dt><span class="sect2"><a href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING">41.11.2. Plan Caching</a></span></dt></dl></div><p>
   3     This section discusses some implementation details that are
   4     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">41.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> function
   7     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 are
  10     syntactically permissible.  As an extreme case, consider
  11     this example of poor programming style:
  12 </p><pre class="programlisting">
  13 INSERT INTO foo (foo) VALUES (foo(foo));
  14 </pre><p>
  15     The first occurrence of <code class="literal">foo</code> must syntactically be a table
  16     name, so it will not be substituted, even if the function has a variable
  17     named <code class="literal">foo</code>.  The second occurrence must be the name of a
  18     column of that table, so it will not be substituted either.  Likewise
  19     the third occurrence must be a function name, so it also will not be
  20     substituted for.  Only the last occurrence is a candidate to be a
  21     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 only
  25     insert data values into an SQL command; it cannot dynamically change which
  26     database objects are referenced by the command.  (If you want to do
  27     that, you must build a command string dynamically, as explained in
  28     <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="41.5.4. Executing Dynamic Commands">Section 41.5.4</a>.)
  29    </p><p>
  30     Since the names of variables are syntactically no different from the names
  31     of table columns, there can be ambiguity in statements that also refer to
  32     tables: is a given name meant to refer to a table column, or a variable?
  33     Let's change the previous example to
  34 </p><pre class="programlisting">
  35 INSERT 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, and
  38     <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 function
  40     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 name
  43     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 telling
  46     <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 a
  50     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 variables
  53     <code class="literal">v_<em class="replaceable"><code>something</code></em></code> while none of your
  54     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 reference
  58     to the table column.  To create an unambiguous reference to a variable,
  59     declare it in a labeled block and use the block's label
  60     (see <a class="xref" href="plpgsql-structure.html" title="41.2. Structure of PL/pgSQL">Section 41.2</a>).  For example,
  61 </p><pre class="programlisting">
  62 &lt;&lt;block&gt;&gt;
  63 DECLARE
  64     foo int;
  65 BEGIN
  66     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 column
  70     <code class="literal">foo</code> in <code class="literal">src</code>.  Function parameters, as well as
  71     special variables such as <code class="literal">FOUND</code>, can be qualified by the
  72     function's name, because they are implicitly declared in an outer block
  73     labeled with the function's name.
  74    </p><p>
  75     Sometimes it is impractical to fix all the ambiguous references in a
  76     large body of <span class="application">PL/pgSQL</span> code.  In such cases you can
  77     specify that <span class="application">PL/pgSQL</span> should resolve ambiguous references
  78     as the variable (which is compatible with <span class="application">PL/pgSQL</span>'s
  79     behavior before <span class="productname">PostgreSQL</span> 9.0), or as the
  80     table column (which is compatible with some other systems such as
  81     <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 configuration
  84     parameter <code class="literal">plpgsql.variable_conflict</code> to one of
  85     <code class="literal">error</code>, <code class="literal">use_variable</code>, or
  86     <code class="literal">use_column</code> (where <code class="literal">error</code> is the factory default).
  87     This parameter affects subsequent compilations
  88     of statements in <span class="application">PL/pgSQL</span> functions, but not statements
  89     already compiled in the current session.
  90     Because changing this setting
  91     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, by
  95     inserting one of these special commands at the start of the function
  96     text:
  97 </p><pre class="programlisting">
  98 #variable_conflict error
  99 #variable_conflict use_variable
 100 #variable_conflict use_column
 101 </pre><p>
 102     These commands affect only the function they are written in, and override
 103     the setting of <code class="literal">plpgsql.variable_conflict</code>.  An example is
 104 </p><pre class="programlisting">
 105 CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
 106     #variable_conflict use_variable
 107     DECLARE
 108         curtime timestamp := now();
 109     BEGIN
 110         UPDATE users SET last_modified = curtime, comment = comment
 111           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 parameters
 117     whether or not <code class="literal">users</code> has columns of those names.  Notice
 118     that we had to qualify the reference to <code class="literal">users.id</code> in the
 119     <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 syntactically
 122     that must be a column of <code class="literal">users</code>.  We could write the same
 123     function without depending on the <code class="literal">variable_conflict</code> setting
 124     in this way:
 125 </p><pre class="programlisting">
 126 CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
 127     &lt;&lt;fn&gt;&gt;
 128     DECLARE
 129         curtime timestamp := now();
 130     BEGIN
 131         UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
 132           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 given
 138     to <code class="command">EXECUTE</code> or one of its variants.  If you need to
 139     insert a varying value into such a command, do so as part of
 140     constructing the string value, or use <code class="literal">USING</code>, as illustrated in
 141     <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" title="41.5.4. Executing Dynamic Commands">Section 41.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 of
 146     these (such as <code class="command">EXPLAIN</code> and <code class="command">CREATE TABLE
 147     ... AS SELECT</code>),
 148     because the main SQL engine allows query parameters only in these
 149     commands.  To use a non-constant name or value in other statement
 150     types (generically called utility statements), you must construct
 151     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">41.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 source
 154     text and produces an internal binary instruction tree the first time the
 155     function is called (within each session).  The instruction tree
 156     fully translates the
 157     <span class="application">PL/pgSQL</span> statement structure, but individual
 158     <acronym class="acronym">SQL</acronym> expressions and <acronym class="acronym">SQL</acronym> commands
 159     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 first
 163     executed in the function, the <span class="application">PL/pgSQL</span> interpreter
 164     parses and analyzes the command to create a prepared statement,
 165     using the <acronym class="acronym">SPI</acronym> manager's
 166     <code class="function">SPI_prepare</code> function.
 167     Subsequent visits to that expression or command
 168     reuse the prepared statement.  Thus, a function with conditional code
 169     paths that are seldom visited will never incur the overhead of
 170     analyzing those commands that are never executed within the current
 171     session.  A disadvantage is that errors
 172     in a specific expression or command cannot be detected until that
 173     part of the function is reached in execution.  (Trivial syntax
 174     errors will be detected during the initial parsing pass, but
 175     anything deeper will not be detected until execution.)
 176    </p><p>
 177     <span class="application">PL/pgSQL</span> (or more precisely, the SPI manager) can
 178     furthermore attempt to cache the execution plan associated with any
 179     particular prepared statement.  If a cached plan is not used, then
 180     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 the
 183     statement has no parameters, or is executed many times, the SPI manager
 184     will consider creating a <em class="firstterm">generic</em> plan that is not dependent
 185     on specific parameter values, and caching that for re-use.  Typically
 186     this will happen only if the execution plan is not very sensitive to
 187     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 of
 189     prepared statements.
 190    </p><p>
 191     Because <span class="application">PL/pgSQL</span> saves prepared statements
 192     and sometimes execution plans in this way,
 193     SQL commands that appear directly in a
 194     <span class="application">PL/pgSQL</span> function must refer to the
 195     same tables and columns on every execution; that is, you cannot use
 196     a parameter as the name of a table or column in an SQL command.  To get
 197     around this restriction, you can construct dynamic commands using
 198     the <span class="application">PL/pgSQL</span> <code class="command">EXECUTE</code>
 199     statement — at the price of performing new parse analysis and
 200     constructing a new execution plan on every execution.
 201    </p><p>
 202      The mutable nature of record variables presents another problem in this
 203      connection.  When fields of a record variable are used in
 204      expressions or statements, the data types of the fields must not
 205      change from one call of the function to the next, since each
 206      expression will be analyzed using the data type that is present
 207      when the expression is first reached.  <code class="command">EXECUTE</code> can be
 208      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 statements
 212      independently for each such table — that is, there is a cache
 213      for each trigger function and table combination, not just for each
 214      function.  This alleviates some of the problems with varying
 215      data types; for instance, a trigger function will be able to work
 216      successfully with a column named <code class="literal">key</code> even if it happens
 217      to have different types in different tables.
 218     </p><p>
 219      Likewise, functions having polymorphic argument types have a separate
 220      statement cache for each combination of actual argument types they have
 221      been invoked for, so that data type differences do not cause unexpected
 222      failures.
 223     </p><p>
 224     Statement caching can sometimes have surprising effects on the
 225     interpretation of time-sensitive values.  For example there
 226     is a difference between what these two functions do:
 227
 228 </p><pre class="programlisting">
 229 CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
 230     BEGIN
 231         INSERT INTO logtable VALUES (logtxt, 'now');
 232     END;
 233 $$ LANGUAGE plpgsql;
 234 </pre><p>
 235
 236      and:
 237
 238 </p><pre class="programlisting">
 239 CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
 240     DECLARE
 241         curtime timestamp;
 242     BEGIN
 243         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>, the
 250      <span class="productname">PostgreSQL</span> main parser knows when
 251      analyzing the <code class="command">INSERT</code> that the
 252      string <code class="literal">'now'</code> should be interpreted as
 253      <code class="type">timestamp</code>, because the target column of
 254      <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 the
 257      <code class="command">INSERT</code> is analyzed, and then used in all
 258      invocations of <code class="function">logfunc1</code> during the lifetime
 259      of the session. Needless to say, this isn't what the programmer
 260      wanted.  A better idea is to use the <code class="literal">now()</code> or
 261      <code class="literal">current_timestamp</code> function.
 262     </p><p>
 263      In the case of <code class="function">logfunc2</code>, the
 264      <span class="productname">PostgreSQL</span> main parser does not know
 265      what type <code class="literal">'now'</code> should become and therefore
 266      it returns a data value of type <code class="type">text</code> containing the string
 267      <code class="literal">now</code>. During the ensuing assignment
 268      to the local variable <code class="varname">curtime</code>, the
 269      <span class="application">PL/pgSQL</span> interpreter casts this
 270      string to the <code class="type">timestamp</code> type by calling the
 271      <code class="function">textout</code> and <code class="function">timestamp_in</code>
 272      functions for the conversion.  So, the computed time stamp is updated
 273      on each execution as the programmer expects.  Even though this
 274      happens to work as expected, it's not terribly efficient, so
 275      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="41.10. Trigger Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-development-tips.html" title="41.12. Tips for Developing in PL/pgSQL">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.10. Trigger Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.12. Tips for Developing in <span class="application">PL/pgSQL</span></td></tr></table></div></body></html>