PL/pgSQL Under the Hood

PostgreSQL 8.4.12 Documentation
Prev | Fast Backward | Chapter 38. PL/pgSQL - SQL Procedural Language | Fast Forward | Next

# 38.10. PL/pgSQL Under the Hood

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

## 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.

> Note: In PostgreSQL 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.

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.

Prev | Home | Next
Trigger Procedures | Up | Tips for Developing in PL/pgSQL