Run dynamic queries

There might be times when you want to generate dynamic queries inside your NZPLSQL procedures. Or, you might have procedures that are designed to generate other procedures. For these situations, NZPLSQL provides the EXECUTE IMMEDIATE statement:

EXECUTE IMMEDIATE query-string

The query-string value is a string of type text which contains the query to be executed.

When working with dynamic queries, make sure that you escape any quotation marks in NZPLSQL.

A query run by an EXECUTE IMMEDIATE statement is prepared each time the statement is run. The query string can be dynamically created within the procedure to do actions on different tables and fields.

The results from SELECT queries are discarded by EXECUTE IMMEDIATE, and SELECT INTO is not currently supported within EXECUTE IMMEDIATE. So, the only way to extract a result from a dynamically created SELECT is to use the FOR ... EXECUTE form, described in Iterate through the records of a query.

An example statement follows:

EXECUTE IMMEDIATE 'UPDATE tbl SET'
|| quote_ident('fieldname')
|| ' = '
|| quote_literal('newvalue')
|| ' WHERE ...';

This example demonstrates the use of the quote_ident and quote_literal functions. To ensure that strings are correctly processed for quotation marks or special characters, expressions that contain column and table identifiers should be passed to quote_ident. Expressions containing values that are literal strings in the constructed command should be passed to quote_literal. Both take the appropriate steps to return the input text enclosed in double quotation marks for quote_ident or quotation marks for quote_literal with any embedded special characters properly escaped.