|PostgreSQL Reference Manual - Volume 2 - Programming Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
RRP £19.95 ($34.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
9.6.5 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 will not work in such
scenarios. To handle this sort of problem, the
EXECUTE statement is provided:
EXECUTE command-string [ INTO [STRICT] target ];
where command-string is an expression
yielding a string (of type
text) containing the
command to be executed and target is a
record variable, row variable, or a comma-separated list of
simple variables and record/row fields.
Note in particular that no substitution of PL/pgSQL variables is done on the computed command string. The values of variables must be inserted in the command string as it is constructed.
Unlike all other commands in PL/pgSQL, a command
run by an
EXECUTE statement is not prepared
and saved just once during the life of the session. Instead, the
command is prepared each time the statement is run. 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. 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.
SELECT INTO is not currently supported within
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.2.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 special 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. For safety,
expressions containing column and table identifiers should be
quote_ident. Expressions containing
values that should be literal strings in the constructed command
should be passed to
take the appropriate steps to return the input text enclosed in
double or single quotes respectively, with any embedded special
characters properly escaped.
Note that dollar quoting is only useful for quoting fixed text. It would be a very bad idea to try to do the above 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
A much larger example of a dynamic command and
EXECUTE can be seen in section 9.11.1 Porting Examples, which builds and executes a
CREATE FUNCTION command to define a new function.
|ISBN 0954612035||PostgreSQL Reference Manual - Volume 2 - Programming Guide||See the print edition|