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.