38.10. PL/pgSQL Under the Hood

38.10.1. Variable Substitution

When PL/pgSQL prepares a SQL statement or expression for execution, any PL/pgSQL variable name appearing in the statement or expression is replaced by a parameter symbol, $n. 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

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

The INSERT statement will effectively be processed as

PREPARE statement_name(text, timestamp) AS
  INSERT INTO logtable VALUES ($1, $2);

followed on each execution by EXECUTE with the current actual values of the two variables. (Note: here we are speaking of the main SQL engine's EXECUTE command, not PL/pgSQL's EXECUTE.)

The substitution mechanism will replace any token that matches a known variable's name. 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 logtable has column names logtxt and logtime, and we try to write the INSERT as

        INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime);

This will be fed to the main SQL parser as

        INSERT INTO logtable ($1, logtime) VALUES ($1, $2);

resulting in a syntax error like this:

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

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:

    DECLARE
        val text;
        search_key integer;
    BEGIN
        ...
        FOR val IN SELECT val FROM table WHERE key = search_key LOOP ...

and wondered why all his table entries seemed to be NULL. Of course what happened here was that the query became

        SELECT $1 FROM table WHERE key = $2

and thus it was just an expensive way of assigning val's current value back to itself for each row.

A commonly used coding rule for avoiding such traps is to use a different naming convention for PL/pgSQL variables than you use for table and column names. For example, if all your variables are named v_something while none of your table or column names start with v_, you're pretty safe.

Another workaround is to use qualified (dotted) names for SQL entities. For instance we could safely have written the above example as

        FOR val IN SELECT table.val FROM table WHERE key = search_key LOOP ...

because PL/pgSQL 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 INSERT'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.

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 Section 38.2). For example,

    <<pl>>
    DECLARE
        val text;
    BEGIN
        ...
        UPDATE table SET col = pl.val WHERE ...

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 "wrong" way. But it is useful for clarifying the intent of potentially-ambiguous code.

Variable substitution does not happen in the 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, as illustrated in Section 38.5.4.

Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE 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 EXECUTE it.

38.10.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 creates a prepared execution plan (using the SPI manager's SPI_prepare and SPI_saveplan 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 PL/pgSQL 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.)

Once PL/pgSQL 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:

CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
    -- declarations
BEGIN
    PERFORM my_function();
END;
$$ LANGUAGE plpgsql;

If you execute the above function, it will reference the OID for my_function() in the execution plan produced for the PERFORM statement. Later, if you drop and recreate my_function(), then populate() will not be able to find my_function() anymore. You would then have to start a new database session so that populate() will be compiled afresh, before it will work again. You can avoid this problem by using CREATE OR REPLACE FUNCTION when updating the definition of my_function, since when a function is "replaced", its OID is not changed.

Because PL/pgSQL saves 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 constructing a new execution plan on every execution.

Another important point is that the prepared plans are parameterized to allow the values of PL/pgSQL 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

SELECT * INTO myrec FROM dictionary WHERE word LIKE search_term;

where search_term is a PL/pgSQL variable. The cached plan for this query will never use an index on word, since the planner cannot assume that the LIKE pattern will be left-anchored at run time. To use an index the query must be planned with a specific constant LIKE pattern provided. This is another situation where EXECUTE can be used to force a new plan to be generated for each 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 planned 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 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 key even if it happens to have different types in different tables.

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.

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:

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 preparing the plan for 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 constant when the INSERT is planned, and then used in all invocations of logfunc1 during the lifetime of the session. Needless to say, this isn't what the programmer wanted.

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 text_out and timestamp_in functions for the conversion. So, the computed time stamp is updated on each execution as the programmer expects.