|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.10.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
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
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;
src must be table names, and
col must be a column of
bar might reasonably be either variables of the function
or columns of
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 9.2 Structure of PL/pgSQL). For example,
<<block>> DECLARE foo int; BEGIN foo := ...; INSERT INTO dest (col) SELECT block.foo + bar FROM src;
block.foo means the variable even if there is a column
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
plpgsql.variable_conflict to one of
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. To set the parameter before
PL/pgSQL has been loaded, it is necessary to have added
plpgsql” to the
custom_variable_classes list in
‘postgresql.conf’. 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;
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
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
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.comment WHERE users.id = stamp_user.id; END; $$ LANGUAGE plpgsql;
Variable substitution does not happen in the command string given
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 9.5.4 Executing Dynamic Commands.
Variable substitution currently works only in
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
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|