|PostgreSQL 8.3.23 Documentation|
|Prev||Fast Backward||Chapter 38. PL/pgSQL - SQL Procedural Language||Fast Forward||Next|
This section discusses some implementation details that are frequently important for PL/pgSQL users to know.
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.
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_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
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
my_function() in the
execution plan produced for the PERFORM statement. Later, if you drop and
populate() will not be able to
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
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;
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
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
the lifetime of the session. Needless to say, this isn't what
the programmer wanted.
In the case of
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
timestamp_in functions for the conversion.
So, the computed time stamp is updated on each execution as the