- publishing free software manuals
PostgreSQL Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
ISBN 0954612035
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.

The 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 INTO variable. If no rows are returned, NULL is assigned to the INTO variable. If no INTO clause is specified, the query results are discarded.

If the STRICT option is given, an error is reported unless the query produces exactly one row.

SELECT INTO is not currently supported within EXECUTE.

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_ident and quote_literal functions. For safety, expressions containing column and table identifiers should be passed to quote_ident. Expressions containing values that should be literal strings in the constructed command should be passed to quote_literal. Both 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 newvalue 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 must use quote_literal.

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 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition