Executing dynamic statements

Non-PL/pgSQL statements are sent to the server engine as regular SQL statements. A few examples in the previous sections have already performed some queries within code blocks. It is possible for a code block to construct an SQL statement dynamically to be executed by the server engine. This can be achieved using the EXECUTE statement.

An EXECUTE statement is made of three parts:

• A mandatory query string, which represents the SQL statement that will be executed
• An optional INTO (or INTO STRICT) clause, which allows us to store values from the previous statement in variables
• An optional USING clause, which provides positional values for parameter substitution

The query string will not be interpolated, which means that no variable substitution will be performed on it. In order to insert variable values into a query string, we must use special positional placeholders, identified by a $ sign and the position starting from 1: $1, $2, $3, and so on. The first positional argument will be substituted with the first value in the USING list, the second with the second value and so on. It is even possible to specify a whole tuple type, using an asterisk after the positional argument, such as $1.*. In this case, however, the corresponding variable in the USING predicate must be of type rowtype. It is possible to use a record, but this requires a more complex and verbose syntax. As an example, Listing 31 shows a dynamically built query with parameter substitution and variable assignment:

testdb=> DO $code$
 DECLARE
   file_name text;
   file_size numeric;
   query_string text;
   file_type text;
 BEGIN
   file_type := 'txt';
   -- build the query to execute
   query_string := 'SELECT f_name, f_size FROM files WHERE f_type = $1 LIMIT 1';

   -- execute the query
   -- and get back the results
   EXECUTE query_string
   INTO STRICT file_name, file_size
   USING file_type;

   RAISE INFO 'File of type % has name % and size % bytes', file_type, file_name, file_size;
 END $code$;

INFO:  File of type txt has name TestFile.txt and size 126525.4400 bytesbytes

Since EXECUTE runs a dynamically created SQL statement, no plan caching is performed against the query string. In other words, the query string does not represent a prepared statement (even if the syntax looks like the one used for prepared statements).

We cannot use positional parameters for the interpolation of identifiers, such as column names. The following code snippet, for example, will not work:

EXECUTE 'SELECT $1, $2 FROM $3'
USING 'f_name', 'f_size', 'files';

If we need to dynamically interpolate identifiers, the built-in format() function can be used. format() works in a similar way to printf(3): it accepts a string template and a list of values to substitute in the template. The template strings accepts two possible placeholders:

• %I for an identifier (such as a column name or a table name)
• %L for a value (such as variable interpolation)

Listing 32 shows a more dynamic way of building and executing a query, where the query string is prepared by format() and then passed to EXECUTE:

testdb=> DO $code$
 DECLARE
   file_name text;
   file_size numeric;
   query_string text;
   file_type text;
 BEGIN
   file_type := 'txt';
   -- build the query to execute
   query_string := format( 'SELECT %I, %I FROM %I WHERE %I = $1 LIMIT 1',  'f_name', 'f_size', 'files', 'f_type' );
   -- execute the query
   -- and get back the results
   EXECUTE query_string
   INTO STRICT file_name, file_size
   USING file_type;

   RAISE INFO 'File of type % has name % and size % bytes', file_type, file_name, file_size;
 END $code$;

INFO:  File of type txt has name TestFile.txt and size 126525.4400 bytesbytes

While format() is a handy function for building dynamic queries from a template, there are a couple of other functions we can use for an even more dynamic approach:

• quote_ident(): This is used to convert an identifier name (such as column or table) into a quoted string (equivalent to format() %I)
• quote_literal(): This is used to quote a value
• quote_nullable(): This is used to quote a value that can be NULL (equivalent to format() %L)

Just by using quote_ident() (which is equivalent to the %I placeholder of format()), Listing 32 can be rewritten as follows:

testdb=> DO $code$
 DECLARE
   file_name text;
   file_size numeric;
   query_string text;
   file_type text;
 BEGIN
   file_type := 'txt';

   -- build the query to execute
   query_string := 'SELECT '
                   || quote_ident( 'f_name' ) || ',' || quote_ident( 'f_size' )
                   || ' FROM ' || quote_ident( 'files' )
                   || ' WHERE ' || quote_ident( 'f_type' ) || ' = $1 LIMIT 1';

   -- execute the query
   -- and get back the results
   EXECUTE query_string
   INTO STRICT file_name, file_size
   USING file_type;

   RAISE INFO 'File of type % has name % and size % bytes',  file_type, file_name, file_size;
 END $code$;

INFO:  File of type txt has name TestFile.txt and size 126525.4400 bytes

We can also go even further and remove the positional parameters, as shown in Listing 34:

testdb=> DO $code$
 DECLARE
   file_name text;
   file_size numeric;
   query_string text;
   file_type text;
 BEGIN
   file_type := 'txt';
   -- build the query to execute
   query_string := 'SELECT '
                   || quote_ident( 'f_name' ) || ',' || quote_ident( 'f_size' )
                   || ' FROM ' || quote_ident( 'files' )
                   || ' WHERE ' || quote_ident( 'f_type' )
                   || ' = ' || quote_literal( file_type )
                   || ' LIMIT 1';

   -- execute the query and get back the results
   EXECUTE query_string
   INTO STRICT file_name, file_size;

   RAISE INFO 'File of type % has name % and size % bytes',                                                   file_type, file_name, file_size; 
 END $code$;

INFO:  File of type txt has name TestFile.txt and size 126525.4400 bytesbytes

As we have seen, there are different ways of executing dynamic statements. As a general rule, we should avoid concatenating values and identifiers into plain strings, because you must pay attention when quoting literals and values, and in general using such a string could lead to SQL injection. To avoid SQL injection and quote problems, identifiers and values should always be interpolated and substituted via either format() or USING. If the SQL statement to be executed has a fixed template, where what it changes is only a condition value or an identifier name, it is preferable to use format(). On the other hand, if the statement must be built a piece at a time, commit to the quote_xxx() set of functions. The resulting query string must be executed via EXECUTE.