<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >PL/pgSQL Under the Hood</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.4.12 Documentation" HREF="index.html"><LINK REL="UP" TITLE="PL/pgSQL - SQL Procedural Language" HREF="plpgsql.html"><LINK REL="PREVIOUS" TITLE="Trigger Procedures" HREF="plpgsql-trigger.html"><LINK REL="NEXT" TITLE="Tips for Developing in PL/pgSQL" HREF="plpgsql-development-tips.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2012-05-31T23:30:11"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.4.12 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="plpgsql-trigger.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="plpgsql.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 38. <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > - <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > Procedural Language</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="plpgsql.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="plpgsql-development-tips.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="PLPGSQL-IMPLEMENTATION" >38.10. <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > Under the Hood</A ></H1 ><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" ><H2 CLASS="SECT2" ><A NAME="PLPGSQL-VAR-SUBST" >38.10.1. Variable Substitution</A ></H2 ><P > When <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > prepares a SQL statement or expression for execution, any <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > variable name appearing in the statement or expression is replaced by a parameter symbol, <TT CLASS="LITERAL" >$<TT CLASS="REPLACEABLE" ><I >n</I ></TT ></TT >. The current value of the variable is then provided as the value for the parameter whenever the statement or expression is executed. As an example, consider the function </P><PRE CLASS="PROGRAMLISTING" >CREATE FUNCTION logfunc(logtxt text) RETURNS void AS $$ DECLARE curtime timestamp := now(); BEGIN INSERT INTO logtable VALUES (logtxt, curtime); END; $$ LANGUAGE plpgsql;</PRE ><P> The <TT CLASS="COMMAND" >INSERT</TT > statement will effectively be processed as </P><PRE CLASS="PROGRAMLISTING" >PREPARE <TT CLASS="REPLACEABLE" ><I >statement_name</I ></TT >(text, timestamp) AS INSERT INTO logtable VALUES ($1, $2);</PRE ><P> followed on each execution by <TT CLASS="COMMAND" >EXECUTE</TT > with the current actual values of the two variables. (Note: here we are speaking of the main SQL engine's <A HREF="sql-execute.html" ><I >EXECUTE</I ></A > command, not <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN >'s <TT CLASS="COMMAND" >EXECUTE</TT >.) </P ><P > <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >The substitution mechanism will replace any token that matches a known variable's name.</I ></SPAN > This poses various traps for the unwary. For example, it is a bad idea to use a variable name that is the same as any table or column name that you need to reference in queries within the function, because what you think is a table or column name will still get replaced. In the above example, suppose that <TT CLASS="STRUCTNAME" >logtable</TT > has column names <TT CLASS="STRUCTFIELD" >logtxt</TT > and <TT CLASS="STRUCTFIELD" >logtime</TT >, and we try to write the <TT CLASS="COMMAND" >INSERT</TT > as </P><PRE CLASS="PROGRAMLISTING" > INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime);</PRE ><P> This will be fed to the main SQL parser as </P><PRE CLASS="PROGRAMLISTING" > INSERT INTO logtable ($1, logtime) VALUES ($1, $2);</PRE ><P> resulting in a syntax error like this: </P><PRE CLASS="SCREEN" >ERROR: syntax error at or near "$1" LINE 1: INSERT INTO logtable ( $1 , logtime) VALUES ( $1 , $2 ) ^ QUERY: INSERT INTO logtable ( $1 , logtime) VALUES ( $1 , $2 ) CONTEXT: SQL statement in PL/PgSQL function "logfunc2" near line 5</PRE ><P> </P ><P > This example is fairly easy to diagnose, since it leads to an obvious syntax error. Much nastier are cases where the substitution is syntactically permissible, since the only symptom may be misbehavior of the function. In one case, a user wrote something like this: </P><PRE CLASS="PROGRAMLISTING" > DECLARE val text; search_key integer; BEGIN ... FOR val IN SELECT val FROM table WHERE key = search_key LOOP ...</PRE ><P> and wondered why all his table entries seemed to be NULL. Of course what happened here was that the query became </P><PRE CLASS="PROGRAMLISTING" > SELECT $1 FROM table WHERE key = $2</PRE ><P> and thus it was just an expensive way of assigning <TT CLASS="LITERAL" >val</TT >'s current value back to itself for each row. </P ><P > A commonly used coding rule for avoiding such traps is to use a different naming convention for <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > variables than you use for table and column names. For example, if all your variables are named <TT CLASS="LITERAL" >v_<TT CLASS="REPLACEABLE" ><I >something</I ></TT ></TT > while none of your table or column names start with <TT CLASS="LITERAL" >v_</TT >, you're pretty safe. </P ><P > Another workaround is to use qualified (dotted) names for SQL entities. For instance we could safely have written the above example as </P><PRE CLASS="PROGRAMLISTING" > FOR val IN SELECT table.val FROM table WHERE key = search_key LOOP ...</PRE ><P> because <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > will not substitute a variable for a trailing component of a qualified name. However this solution does not work in every case — you can't qualify a name in an <TT CLASS="COMMAND" >INSERT</TT >'s column name list, for instance. Another point is that record and row variable names will be matched to the first components of qualified names, so a qualified SQL name is still vulnerable in some cases. In such cases choosing a non-conflicting variable name is the only way. </P ><P > Another technique you can use is to attach a label to the block in which your variables are declared, and then qualify the variable names in your SQL commands (see <A HREF="plpgsql-structure.html" >Section 38.2</A >). For example, </P><PRE CLASS="PROGRAMLISTING" > <<pl>> DECLARE val text; BEGIN ... UPDATE table SET col = pl.val WHERE ...</PRE ><P> This is not in itself a solution to the problem of conflicts, since an unqualified name in a SQL command is still at risk of being interpreted the <SPAN CLASS="QUOTE" >"wrong"</SPAN > way. But it is useful for clarifying the intent of potentially-ambiguous code. </P ><P > Variable substitution does not happen in the command string given to <TT CLASS="COMMAND" >EXECUTE</TT > 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, as illustrated in <A HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" >Section 38.5.4</A >. </P ><P > Variable substitution currently works only in <TT CLASS="COMMAND" >SELECT</TT >, <TT CLASS="COMMAND" >INSERT</TT >, <TT CLASS="COMMAND" >UPDATE</TT >, and <TT CLASS="COMMAND" >DELETE</TT > commands, because the main SQL engine allows parameter symbols 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 <TT CLASS="COMMAND" >EXECUTE</TT > it. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="PLPGSQL-PLAN-CACHING" >38.10.2. Plan Caching</A ></H2 ><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 > As each expression and <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > command is first executed in the function, the <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > interpreter creates a prepared execution plan (using the <ACRONYM CLASS="ACRONYM" >SPI</ACRONYM > manager's <CODE CLASS="FUNCTION" >SPI_prepare</CODE > and <CODE CLASS="FUNCTION" >SPI_saveplan</CODE > functions).<A NAME="AEN48649" ></A > Subsequent visits to that expression or command reuse the prepared plan. Thus, a function with conditional code that contains many statements for which execution plans might be required will only prepare and save those plans that are really used during the lifetime of the database connection. This can substantially reduce the total amount of time required to parse and generate execution plans for the statements in a <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > function. 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 > Once <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > has made an execution plan for a particular command in a function, it will reuse that plan for the life of the database connection. This is usually a win for performance, but it can cause some problems if you dynamically alter your database schema. For example: </P><PRE CLASS="PROGRAMLISTING" >CREATE FUNCTION populate() RETURNS integer AS $$ DECLARE -- declarations BEGIN PERFORM my_function(); END; $$ LANGUAGE plpgsql;</PRE ><P> If you execute the above function, it will reference the OID for <CODE CLASS="FUNCTION" >my_function()</CODE > in the execution plan produced for the <TT CLASS="COMMAND" >PERFORM</TT > statement. Later, if you drop and recreate <CODE CLASS="FUNCTION" >my_function()</CODE >, then <CODE CLASS="FUNCTION" >populate()</CODE > will not be able to find <CODE CLASS="FUNCTION" >my_function()</CODE > anymore. You would then have to start a new database session so that <CODE CLASS="FUNCTION" >populate()</CODE > will be compiled afresh, before it will work again. You can avoid this problem by using <TT CLASS="COMMAND" >CREATE OR REPLACE FUNCTION</TT > when updating the definition of <CODE CLASS="FUNCTION" >my_function</CODE >, since when a function is <SPAN CLASS="QUOTE" >"replaced"</SPAN >, its OID is not changed. </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > In <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > 8.3 and later, saved plans will be replaced whenever any schema changes have occurred to any tables they reference. This eliminates one of the major disadvantages of saved plans. However, there is no such mechanism for function references, and thus the above example involving a reference to a deleted function is still valid. </P ></BLOCKQUOTE ></DIV ><P > Because <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > saves 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 > <TT CLASS="COMMAND" >EXECUTE</TT > statement — at the price of constructing a new execution plan on every execution. </P ><P > Another important point is that the prepared plans are parameterized to allow the values of <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > variables to change from one use to the next, as discussed in detail above. Sometimes this means that a plan is less efficient than it would be if generated for a specific variable value. As an example, consider </P><PRE CLASS="PROGRAMLISTING" >SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;</PRE ><P> where <TT CLASS="LITERAL" >search_term</TT > is a <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > variable. The cached plan for this query will never use an index on <TT CLASS="STRUCTFIELD" >word</TT >, since the planner cannot assume that the <TT CLASS="LITERAL" >LIKE</TT > pattern will be left-anchored at run time. To use an index the query must be planned with a specific constant <TT CLASS="LITERAL" >LIKE</TT > pattern provided. This is another situation where <TT CLASS="COMMAND" >EXECUTE</TT > can be used to force a new plan to be generated for each 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 planned using the data type that is present when the expression is first reached. <TT CLASS="COMMAND" >EXECUTE</TT > 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 plans 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 <TT CLASS="LITERAL" >key</TT > even if it happens to have different types in different tables. </P ><P > Likewise, functions having polymorphic argument types have a separate plan 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 > Plan 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 preparing the plan for the <TT CLASS="COMMAND" >INSERT</TT > that the string <TT CLASS="LITERAL" >'now'</TT > should be interpreted as <TT CLASS="TYPE" >timestamp</TT >, because the target column of <CODE CLASS="CLASSNAME" >logtable</CODE > is of that type. Thus, <TT CLASS="LITERAL" >'now'</TT > will be converted to a constant when the <TT CLASS="COMMAND" >INSERT</TT > is planned, 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. </P ><P > In the case of <CODE CLASS="FUNCTION" >logfunc2</CODE >, the <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > main parser does not know what type <TT CLASS="LITERAL" >'now'</TT > should become and therefore it returns a data value of type <TT CLASS="TYPE" >text</TT > containing the string <TT CLASS="LITERAL" >now</TT >. During the ensuing assignment to the local variable <TT CLASS="VARNAME" >curtime</TT >, the <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN > interpreter casts this string to the <TT CLASS="TYPE" >timestamp</TT > type by calling the <CODE CLASS="FUNCTION" >text_out</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. </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="plpgsql-trigger.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="plpgsql-development-tips.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Trigger Procedures</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="plpgsql.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Tips for Developing in <SPAN CLASS="APPLICATION" >PL/pgSQL</SPAN ></TD ></TR ></TABLE ></DIV ></BODY ></HTML >