|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.5.4 Executing Dynamic Commands
Oftentimes you will want to generate dynamic commands inside your
PL/pgSQL functions, that is, commands
that will involve different tables or different data types each
time they are executed. PL/pgSQL's
normal attempts to cache plans for commands (as discussed in
section 9.10.2 Plan Caching) will not work in such
scenarios. To handle this sort of problem, the
EXECUTE statement is provided:
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
where command-string is an expression
yielding a string (of type
text) containing the
command to be executed. The optional target
is a record variable, a row variable, or a comma-separated list of
simple variables and record/row fields, into which the results of
the command will be stored. The optional
supply values to be inserted into the command.
No substitution of PL/pgSQL variables is done on the computed command string. Any required variable values must be inserted in the command string as it is constructed; or you can use parameters as described below.
Also, there is no plan caching for commands executed via
EXECUTE. Instead, the
command is prepared each time the statement is run. Thus the command
string can be dynamically created within the function to perform
actions on different tables and columns.
INTO clause specifies where the results of
a SQL command returning rows should be assigned. If a row
or variable list is provided, it must exactly match the structure
of the query's results (when a
record variable is used, it will configure itself to match the
result structure automatically). If multiple rows are returned,
only the first will be assigned to the
variable. If no rows are returned, NULL is assigned to the
INTO variable(s). If no
clause is specified, the query results are discarded.
STRICT option is given, an error is reported
unless the query produces exactly one row.
The command string can use parameter values, which are referenced
in the command as
These symbols refer to values supplied in the
clause. This method is often preferable to inserting data values
into the command string as text: it avoids run-time overhead of
converting the values to text and back, and it is much less prone
to SQL-injection attacks since there is no need for quoting or escaping.
An example is:
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
Note that parameter symbols can only be used for data values--if you want to use dynamically determined table or column names, you must insert them into the command string textually. For example, if the preceding query needed to be done against a dynamically selected table, you could do this:
EXECUTE 'SELECT count(*) FROM ' || tabname::regclass || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
Another restriction on parameter symbols is that they only work in
DELETE commands. In other statement
types (generically called utility statements), you must insert
values textually even if they are just data values.
EXECUTE with a simple constant command string and some
USING parameters, as in the first example above, is
functionally equivalent to just writing the command directly in
PL/pgSQL and allowing replacement of
PL/pgSQL variables to happen automatically.
The important difference is that
EXECUTE will re-plan
the command on each execution, generating a plan that is specific
to the current parameter values; whereas
PL/pgSQL normally creates a generic plan
and caches it for re-use. In situations where the best plan depends
strongly on the parameter values,
EXECUTE can be
significantly faster; while when the plan is not sensitive to parameter
values, re-planning will be a waste.
SELECT INTO is not currently supported within
EXECUTE; instead, execute a plain
command and specify
INTO as part of the
Note: The PL/pgSQL
EXECUTEstatement is not related to the Volume 1B: 1.107 EXECUTE SQL statement supported by the PostgreSQL server. The server's
EXECUTEstatement cannot be used directly within PL/pgSQL functions (and is not needed).
Quoting values in dynamic queries:
When working with dynamic commands you will often have to handle escaping of single quotes. The recommended method for quoting fixed text in your function body is dollar quoting. (If you have legacy code that does not use dollar quoting, please refer to the overview in section 9.11.1 Handling of Quotation Marks, which can save you some effort when translating said code to a more reasonable scheme.)
Dynamic values that are to be inserted into the constructed query require careful handling since they might themselves contain quote characters. An example (this assumes that you are using dollar quoting for the function as a whole, so the quote marks need not be doubled):
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);
This example demonstrates the use of the
quote_literal functions (see Volume 1A: 7.4 String Functions and Operators). For safety, expressions containing column
or table identifiers should be passed through
quote_ident before insertion in a dynamic query.
Expressions containing values that should be literal strings in the
constructed command should be passed through
These functions take the appropriate steps to return the input text
enclosed in double or single quotes respectively, with any embedded
special characters properly escaped.
quote_literal is labelled
STRICT, it will always return null when called with a
null argument. In the above example, if
keyvalue were null, the entire dynamic query string would
become null, leading to an error from
You can avoid this problem by using the
function, which works the same as
quote_literal except that
when called with a null argument it returns the string
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_nullable(newvalue) || ' WHERE key = ' || quote_nullable(keyvalue);
If you are dealing with values that might be null, you should usually
quote_nullable in place of
As always, care must be taken to ensure that null values in a query do
not deliver unintended results. For example the
'WHERE key = ' || quote_nullable(keyvalue)
will never succeed if
keyvalue is null, because the
result of using the equality operator
= with a null operand
is always null. If you wish null to work like an ordinary key value,
you would need to rewrite the above as
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
IS NOT DISTINCT FROM is handled much less
=, so don't do this unless you must.
See Volume 1A: 7.2 Comparison Operators for
more information on nulls and
Note that dollar quoting is only useful for quoting fixed text. It would be a very bad idea to try to write this example as:
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue);
because it would break if the contents of
happened to contain
$$. The same objection would
apply to any other dollar-quoting delimiter you might pick.
So, to safely quote text that is not known in advance, you
quote_ident, as appropriate.
A much larger example of a dynamic command and
EXECUTE can be seen in section 9.12.1 Porting Examples, which builds and executes a
CREATE FUNCTION command to define a new function.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|