Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / txt / plpgsql-implementation.txt

41.11. PL/pgSQL under the Hood #

   41.11.1. Variable Substitution
   41.11.2. Plan Caching

   This section discusses some implementation details that are frequently
   important for PL/pgSQL users to know.

41.11.1. Variable Substitution #

   SQL statements and expressions within a PL/pgSQL function can refer to
   variables and parameters of the function. Behind the scenes, PL/pgSQL
   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:
INSERT INTO foo (foo) VALUES (foo(foo));

   The first occurrence of foo must syntactically be a table name, so it
   will not be substituted, even if the function has a variable named foo.
   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
   PL/pgSQL function.

   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
   Section 41.5.4.)

   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
INSERT INTO dest (col) SELECT foo + bar FROM src;

   Here, dest and src must be table names, and col must be a column of
   dest, but foo and bar might reasonably be either variables of the
   function or columns of src.

   By default, PL/pgSQL 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 PL/pgSQL which interpretation to
   prefer.

   The simplest solution is to rename the variable or column. A common
   coding rule is to use a different naming convention for PL/pgSQL
   variables than you use for column names. For example, if you
   consistently name function variables v_something while none of your
   column names start with v_, no conflicts will occur.

   Alternatively you can qualify ambiguous references to make them clear.
   In the above example, src.foo 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 Section 41.2). For
   example,
<<block>>
DECLARE
    foo int;
BEGIN
    foo := ...;
    INSERT INTO dest (col) SELECT block.foo + bar FROM src;

   Here block.foo means the variable even if there is a column foo in src.
   Function parameters, as well as special variables such as FOUND, can be
   qualified by the function's name, because they are implicitly declared
   in an outer block labeled with the function's name.

   Sometimes it is impractical to fix all the ambiguous references in a
   large body of PL/pgSQL code. In such cases you can specify that
   PL/pgSQL should resolve ambiguous references as the variable (which is
   compatible with PL/pgSQL's behavior before PostgreSQL 9.0), or as the
   table column (which is compatible with some other systems such as
   Oracle).

   To change this behavior on a system-wide basis, set the configuration
   parameter plpgsql.variable_conflict to one of error, use_variable, or
   use_column (where error is the factory default). This parameter affects
   subsequent compilations of statements in PL/pgSQL functions, but not
   statements already compiled in the current session. Because changing
   this setting can cause unexpected changes in the behavior of PL/pgSQL
   functions, it can only be changed by a superuser.

   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:
#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

   These commands affect only the function they are written in, and
   override the setting of plpgsql.variable_conflict. An example is
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;

   In the UPDATE command, curtime, comment, and id will refer to the
   function's variable and parameters whether or not users has columns of
   those names. Notice that we had to qualify the reference to users.id in
   the WHERE clause to make it refer to the table column. But we did not
   have to qualify the reference to comment as a target in the UPDATE
   list, because syntactically that must be a column of users. We could
   write the same function without depending on the variable_conflict
   setting in this way:
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.commen
t
          WHERE users.id = stamp_user.id;
    END;
$$ LANGUAGE plpgsql;

   Variable substitution does not happen in a command string given to
   EXECUTE 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 USING, as illustrated in Section 41.5.4.

   Variable substitution currently works only in SELECT, INSERT, UPDATE,
   DELETE, and commands containing one of these (such as EXPLAIN and
   CREATE TABLE ... AS SELECT), 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 EXECUTE it.

41.11.2. Plan Caching #

   The PL/pgSQL 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
   PL/pgSQL statement structure, but individual SQL expressions and SQL
   commands used in the function are not translated immediately.

   As each expression and SQL command is first executed in the function,
   the PL/pgSQL interpreter parses and analyzes the command to create a
   prepared statement, using the SPI manager's SPI_prepare 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.)

   PL/pgSQL (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, PL/pgSQL 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 generic 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 PL/pgSQL variables referenced in
   it. If it is, generating a plan each time is a net win. See PREPARE for
   more information about the behavior of prepared statements.

   Because PL/pgSQL saves prepared statements and sometimes execution
   plans in this way, SQL commands that appear directly in a PL/pgSQL
   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 PL/pgSQL EXECUTE statement — at the price of
   performing new parse analysis and constructing a new execution plan on
   every execution.

   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. EXECUTE can be used to get around this problem when necessary.

   If the same function is used as a trigger for more than one table,
   PL/pgSQL 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 key even if it happens
   to have different types in different tables.

   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.

   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:
CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql;

   and:
CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

   In the case of logfunc1, the PostgreSQL main parser knows when
   analyzing the INSERT that the string 'now' should be interpreted as
   timestamp, because the target column of logtable is of that type. Thus,
   'now' will be converted to a timestamp constant when the INSERT is
   analyzed, and then used in all invocations of logfunc1 during the
   lifetime of the session. Needless to say, this isn't what the
   programmer wanted. A better idea is to use the now() or
   current_timestamp function.

   In the case of logfunc2, the PostgreSQL main parser does not know what
   type 'now' should become and therefore it returns a data value of type
   text containing the string now. During the ensuing assignment to the
   local variable curtime, the PL/pgSQL interpreter casts this string to
   the timestamp type by calling the textout and timestamp_in 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 now() function
   would still be a better idea.