PL/pgSQL
Dynamic Commands
12
Copyright
© Postgres Professional, 2017–2021
Authors: Egor Rogov, Pavel Luzanov
Translated by Liudmila Mantrova
Usage of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed on an unrestricted basis. Commercial use is only possible with prior
written permission of Postgres Professional company. Modification of course
materials is forbidden.
Contact Us
Please send your feedback to: edu@postgrespro.ru
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
Agenda
Motivation
Executing dynamic queries
Constructing dynamic queries
3
Dynamic SQL
The text of an SQL command is constructed at run time
Motivation
provide additional flexibility for an application
construct several specific queries for optimization purposes
instead of using a single query that covers all possible cases
Cost
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 when the application is running.
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
specified parameters 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 find all executable commands in source
code using full-text search.
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
Executing Dynamic Queries
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 (which is covered in lecture “Architecture.
A General Overview of PostgreSQL”). 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.
Note the following fact: a procedure cannot perform transaction control if it is
called by the EXECUTE operator.
6
Constructing Commands
Substituting parameter values
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 in the case of correct use
Regular string functions
concatenation, etc.
there is a 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. But it should be done with great care as there is a risk of
SQL injection.
If the values are passes 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
Summary
Dynamic commands provide additional flexibility
Constructing separate queries for different parameter values
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.
Task 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
real life 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 the matrix report on functions
available in the database.
The columns must contain names of function owners, the rows
must provide schema names, while the fields must show the
number of functions that belong to a particular owner in a
particular schema.
What statement should you write to invoke this function?
Task 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 in advance. So it is
required 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.
