Dynamic SQL is used to build and execute queries on the fly. Unlike the static SQL statement, a dynamic SQL statement's full text is unknown and can change between successive executions. These queries can be DDL, DCL, and DML statements. Dynamic SQL is used to reduce repetitive tasks. For example, one could use dynamic SQL to create table partitioning for a certain table on a daily basis, to add missing indexes on all foreign keys, or add data auditing capabilities to a certain table without major coding effects. Another important use of dynamic SQL is to overcome the side effects of PL/pgSQL caching, as queries executed using the EXECUTE statement are not cached.
Dynamic SQL is achieved via the EXECUTE statement. The EXECUTE statement accepts a string and simply evaluates it. The synopsis to execute a statement is given as follows:
EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ...] ];