PL/pgSQL
Dynamic Commands
17
Copyright
© Postgres Professional, 2017–2025
Authors Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by Liudmila Mantrova and Alexander Meleshko
Photo by Oleg Bartunov (Tukuche peak, Nepal)
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.com
Disclaimer
In no event shall Postgres Professional company be liable for any damages
or loss, including loss of profits, that arise from direct or indirect, special or
incidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Objectives
Executing dynamic queries
Constructing dynamic queries
3
Dynamic SQL
The text of an SQL command is constructed at run time
Objectives
provide additional flexibility for an application
construct several specific queries for optimization purposes
instead of using a single query that covers all possible cases
Trade-off
statements cannot be prepared
the risk of SQL injection rises
maintenance gets more complicated
An SQL command is considered dynamic if its text is constructed and then
executed within PL/pgSQL routine blocks or in anonymous blocks.
In most cases, you can do without dynamic commands, but sometimes they
can provide additional flexibility. For example, an application can have a
built-in capability to execute commands provided via system settings.
Instead of being hard-coded by developers, these settings can be tuned by
the support team in production.
When creating reports with a large number of optional parameters, it is
sometimes easier to construct the text of a query at run time for the provided
arguments only, instead of creating a complex query that includes all
possible parameter combinations while developing the application.
The price you pay for using dynamic commands is inability to take
advantage of prepared statements, which are available in PL/pgSQL.
Besides, you have to take care of dynamic commands’ security as they are
vulnerable to SQL injection.
We should also mention that maintenance gets more complicated. In
particular, it will be impossible to scan the source code quickly for
executable commands with tools like grep.
4
EXECUTE operator
runs a text representation of an SQL query
allows using parameters
PL/pgSQL variables do not become implicit parameters
Can be used instead of an SQL query
independently
when opening a cursor
in a loop over a query
in the RETURN QUERY clause
Query execution
To run dynamic commands, PL/pgSQL uses the EXECUTE operator that
launches the SQL operator provided as a text string.
A dynamic query can contain explicit parameters. In the command’s text
representation, parameters are denoted by $1, $2, etc.; their actual values
are provided in the USING clause. Parameters are handled in the same way
as in prepared statements. However, PL/pgSQL variables do not become
implicit parameters, as it happens in the case of regular (as opposed to
dynamic) use of SQL in PL/pgSQL.
The EXECUTE operator can be used on its own (it will simply execute a
dynamic command). It can also be used in loops over queries, when
opening a cursor, or in the RETURN QUERY command: in all these cases,
EXECUTE replaces the SQL operator.
https://postgrespro.com/docs/postgresql/17/plpgsql-statements#PLPGSQL-
STATEMENTS-EXECUTING-DYN
Note that a procedure cannot perform transaction control if it is called by the
EXECUTE operator.
6
Constructing commands
Parameter values binding
USING clause
guarantees protection against SQL injection
Escaping values
identifiers: format('%I'), quote_ident
literals: format('%L'), quote_literal, quote_nullable
SQL injection is impossible if implemented correctly
Regular string functions
concatenation, etc.
risk of SQL injection!
Using the EXECUTE operator makes sense if the command is constructed
dynamically. The previous examples could also do without EXECUTE.
Since the command is represented by a text string, it can be constructed
using regular string functions that perform such operations as concatenation,
etc. This should be done with great care as there is a risk of SQL injection.
If the values are passed as parameters in the USING clause, SQL injection
is technically impossible.
However, it is not always possible to use parameters: you may have to
concatenate specific parts of the query or insert a table name into the query.
In this case, you should escape the values received from an unreliable
source to protect your application against injections.
Identifiers are generated by either the format function with the %l specifier or
the quote_ident function. These functions ensure that identifiers have
valid names by double-quoting them and escaping special characters, if
required.
To insert literals into the command text, you can use either quote_literal
and quote_nullable functions or the format function with the %L specifier.
https://postgrespro.com/docs/postgresql/17/functions-string
8
Takeaways
Dynamic commands provide additional flexibility
Constructing separate queries for different arguments can
improve performance
Dynamic commands are not suitable for short, frequently used
queries
Maintenance gets more complicated
9
Practice
1. Modify the get_catalog function so that the query
to the catalog_v view is constructed dynamically and takes
into account only those fields that are filled out in the search
form of the Store tab.
Make sure that your implementation is protected against SQL
injection.
Check your function in the application.
1. Suppose we have to generate the following query if these conditions are
met: the “In stock” option is selected in the search form, but “Book Title” and
“Author” fields are empty.
SELECT ... FROM catalog_v WHERE onhand_qty > 0;
You should keep in mind that this implementation will not necessarily speed
up search, but it will certainly be harder to maintain. Avoid such solutions in
production environments unless you have a solid reason to use this
technique. To learn more about query performance tuning, check out the
QPT course.