43.11. PL/pgSQL Under
the Hood

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

43.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. 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:

INSERT INTO foo (foo) VALUES (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 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.

Note

PostgreSQL versions
before 9.0 would try to substitute the variable in all three
cases, leading to syntax errors.

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

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:

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:

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, or use
USING, as illustrated in Section 43.5.4.

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

43.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/pgSQLEXECUTE 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:

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

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.