PL/pgSQL
Dynamic Commands
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo by: Oleg Bartunov (Phu monastery, Bhrikuti summit, 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.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and losses,
including loss of income, caused by direct or indirect, intentional or accidental
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 Commands
3
Dynamic SQL
The text of an SQL query is constructed at run time
Objectives
provide additional flexibility for the application
construct several specific queries for optimization purposes
instead of using a single query that covers all possible cases
Trade-off
statements are not 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 used in PL/pgSQL by default.
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 construct
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 statement
Query Execution
To run dynamic commands, PL/pgSQL uses the EXECUTE construct which
launches the SQL command 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 (which is covered in Architecture. PostgreSQL
Fundamentals). 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 construct can be used as an independent statement (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 statement: in all
these cases, EXECUTE replaces the SQL statement.
Note that a procedure cannot perform transaction control if it is called by an
EXECUTE construct.
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 construct 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.
8
Takeaways
Dynamic commands provide additional flexibility
Constructing separate queries for different arguments can
improve performance
Dynamic commands are not suitable for short, frequent 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.
10
Practice+
1. Create a function that returns a matrix report on functions
available in the database.
The columns must contain names of function owners, the rows
must provide schema names, while the cells must display the
number of functions that belong to a particular owner in a
particular schema.
What statement should you write to invoke this function?
1. Here is a possible output:
schema total postgres student ...
information_schema 12 12 0
pg_catalog 2811 2811 0
public 3 0 3
...
The number of columns returned by a query is unknown beforehand. You
need to construct a query and then execute it dynamically. The query text
can be as follows:
SELECT pronamespace::regnamespace::text AS schema,
COUNT(*) AS total
,SUM(CASE WHEN proowner = 10 THEN 1 ELSE 0 END) postgres
,SUM(CASE WHEN proowner = 16384 THEN 1 ELSE 0 END) student
FROM pg_proc
GROUP BY pronamespace::regnamespace
ORDER BY schema
The highlighted lines are a dynamic part that has to be constructed by a
separate query. The start and the end of the query are static.
The proowner column has the oid type. To get the name of the owner, you
can use the following construct: proowner::regrole::text.
