~ubuntu-branches/ubuntu/wily/postgresql-9.1/wily : contents of doc/src/sgml/html/plpgsql-implementation.html at revision 24

~ubuntu-branches/ubuntu/wily/postgresql-9.1/wily : (revision 24)
<!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 9.1.5 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-08-14T22:55:48"></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"
><A
HREF="index.html"
>PostgreSQL 9.1.5 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Trigger Procedures"
HREF="plpgsql-trigger.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 39. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> - <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Procedural Language</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Tips for Developing in PL/pgSQL"
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"
>39.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"
>39.10.1. Variable Substitution</A
></H2
><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.
    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:
</P><PRE
CLASS="PROGRAMLISTING"
>INSERT INTO foo (foo) VALUES (foo);</PRE
><P>
    The first occurrence of <TT
CLASS="LITERAL"
>foo</TT
> must syntactically be a table
    name, so it will not be substituted, even if the function has a variable
    named <TT
CLASS="LITERAL"
>foo</TT
>.  The second occurrence must be the name of a
    column of the table, so it will not be substituted either.  Only the
    third occurrence is a candidate to be a reference to the function's
    variable.
   </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>     <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> versions before 9.0 would try
     to substitute the variable in all three cases, leading to syntax errors.
    </P
></BLOCKQUOTE
></DIV
><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, <TT
CLASS="LITERAL"
>dest</TT
> and <TT
CLASS="LITERAL"
>src</TT
> must be table names, and
    <TT
CLASS="LITERAL"
>col</TT
> must be a column of <TT
CLASS="LITERAL"
>dest</TT
>, but <TT
CLASS="LITERAL"
>foo</TT
>
    and <TT
CLASS="LITERAL"
>bar</TT
> might reasonably be either variables of the function
    or columns of <TT
CLASS="LITERAL"
>src</TT
>.
   </P
><P
>    By default, <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> will report an error if a name
    in a 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
    <TT
CLASS="LITERAL"
>v_<TT
CLASS="REPLACEABLE"
><I
>something</I
></TT
></TT
> while none of your
    column names start with <TT
CLASS="LITERAL"
>v_</TT
>, no conflicts will occur.
   </P
><P
>    Alternatively you can qualify ambiguous references to make them clear.
    In the above example, <TT
CLASS="LITERAL"
>src.foo</TT
> 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
HREF="plpgsql-structure.html"
>Section 39.2</A
>).  For example,
</P><PRE
CLASS="PROGRAMLISTING"
>&lt;&lt;block&gt;&gt;
DECLARE
    foo int;
BEGIN
    foo := ...;
    INSERT INTO dest (col) SELECT block.foo + bar FROM src;</PRE
><P>
    Here <TT
CLASS="LITERAL"
>block.foo</TT
> means the variable even if there is a column
    <TT
CLASS="LITERAL"
>foo</TT
> in <TT
CLASS="LITERAL"
>src</TT
>.  Function parameters, as well as
    special variables such as <TT
CLASS="LITERAL"
>FOUND</TT
>, 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
><P
>    To change this behavior on a system-wide basis, set the configuration
    parameter <TT
CLASS="LITERAL"
>plpgsql.variable_conflict</TT
> to one of
    <TT
CLASS="LITERAL"
>error</TT
>, <TT
CLASS="LITERAL"
>use_variable</TT
>, or
    <TT
CLASS="LITERAL"
>use_column</TT
> (where <TT
CLASS="LITERAL"
>error</TT
> 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.  To set the parameter before
    <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> has been loaded, it is necessary to have added
    <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>plpgsql</TT
>"</SPAN
> to the <A
HREF="runtime-config-custom.html#GUC-CUSTOM-VARIABLE-CLASSES"
>custom_variable_classes</A
> list in
    <TT
CLASS="FILENAME"
>postgresql.conf</TT
>.  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 <TT
CLASS="LITERAL"
>plpgsql.variable_conflict</TT
>.  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 <TT
CLASS="LITERAL"
>UPDATE</TT
> command, <TT
CLASS="LITERAL"
>curtime</TT
>, <TT
CLASS="LITERAL"
>comment</TT
>,
    and <TT
CLASS="LITERAL"
>id</TT
> will refer to the function's variable and parameters
    whether or not <TT
CLASS="LITERAL"
>users</TT
> has columns of those names.  Notice
    that we had to qualify the reference to <TT
CLASS="LITERAL"
>users.id</TT
> in the
    <TT
CLASS="LITERAL"
>WHERE</TT
> clause to make it refer to the table column.
    But we did not have to qualify the reference to <TT
CLASS="LITERAL"
>comment</TT
>
    as a target in the <TT
CLASS="LITERAL"
>UPDATE</TT
> list, because syntactically
    that must be a column of <TT
CLASS="LITERAL"
>users</TT
>.  We could write the same
    function without depending on the <TT
CLASS="LITERAL"
>variable_conflict</TT
> setting
    in this way:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    &lt;&lt;fn&gt;&gt;
    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 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, or use <TT
CLASS="LITERAL"
>USING</TT
>, as illustrated in
    <A
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN"
>Section 39.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 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 <TT
CLASS="COMMAND"
>EXECUTE</TT
> it.
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PLPGSQL-PLAN-CACHING"
>39.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).
    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
>    A saved plan will be re-planned automatically if there is any schema
    change to any table used in the query, or if any user-defined function
    used in the query is redefined.  This makes the re-use of prepared plans
    transparent in most cases, but there are corner cases where a stale plan
    might be re-used.  An example is that dropping and re-creating a
    user-defined operator won't affect already-cached plans; they'll continue
    to call the original operator's underlying function, if that has not been
    changed.  When necessary, the cache can be flushed by starting a fresh
    database session.
   </P
><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 &mdash; 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 &mdash; 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
>