Redshift Dynamic SQL Queries and Examples

When you are working on the various data sources and different kind of SQL queries, there may be situations where you want to generate dynamic SQL queries dynamically based on your requirement. In Redshift you can PREPARE SQL statement and EXECUTE it. Redshift also support use of EXECUTE command in Redshift stored procedures. In this article, we will check Redshift Dynamic SQL Queries, how to PREPARE them and run using EXECUTE statements.

The dynamic SQL in Redshift can be useful in many scenarios, such as when you need to execute different SQL statements based on varying input data, when you need to perform repetitive database operations, or when you need to build complex SQL statements in a programmatic manner.

Before jumping into Amazon dynamic SQL, let us first check what are the advantages and disadvantages of using dynamic SQL queries in Amazon Redshift.

Benefits and Drawbacks of Using Amazon Redshift Dynamic SQL

Benefits of Using Amazon Redshift Dynamic SQL

There are several advantages of using dynamic SQL in Amazon Redshift.

The main advantages of dynamic SQL statements are that it can be built interactively with input from users having little or no knowledge of SQL.
The dynamically defined SQL statements are more versatile than plain embedded SQL programs.
Amazon Redshift Dynamic SQL is flexible and parameterized for usage.
With dynamic SQL, you can create a single SQL statement that performs several tasks

Drawbacks of Using Amazon Redshift Dynamic SQL

There are few disadvantages of using dynamic SQL in Amazon Redshift.

Some Amazon Redshift dynamic queries require complex coding, the use of special data structures, and more runtime processing.
Dynamic SQL in general are at higher risk of SQL injection, if not used properly.
It is difficult to validate the dynamic SQL query as it’s created as the code is run, and hence a higher chance of errors.

Redshift Dynamic SQL Queries

As mentioned earlier, you can execute a dynamic SQL directly or inside your stored procedure based on your requirement. Inside stored procedure, you can directly execute a dynamic SQL using EXECUTE command. However, outside Redshift SP, you have to prepare the SQL plan and execute that using EXECUTE command.

Redshift PREPARE Statement

The PREPARE statement is used to prepare a SQL statement for execution.

The PREPARE statement supports SELECT, INSERT, UPDATE or DELETE statements. You can use any of the mentioned statements in your dynamic query. When you execute the PREPARE statement, the specified statement is parsed, rewritten, and planned. Once a statement is prepared, you just issue EXECUTE command to run it.

Redshift PREPARE Statement Syntax

Below is the PREPARE statement syntax:

PREPARE plan_name [ (datatype [, ...] ) ] AS statement

Prepared statements can take parameters. These parameter are substituted dynamically into statements when executed using EXECUTE command.

To include parameters in a prepared statement, you can supply a list of data types in the PREPARE statement, and refer to the parameters by position using the notation $1, $2, … When executing the statement, specify the actual values for these parameters in the EXECUTE command.

Redshift EXECUTE Dynamic SQL Statement

The EXECUTE command is used to execute previously prepared statement using PREPARE command.

Redshift EXECUTE Statement Syntax

Below is the EXECUTE statement syntax:

EXECUTE plan_name [ (parameter [, ...]) ]

Note that plan should be prepared before issuing EXECUTE command. otherwise, Redshift will throw error.

Redshift DEALLOCATE Statement

DEALLOCATE command is used to deallocate previously prepared plan. This statement will be usually at the end of your query text.

DEALLOCATE plan_name

Redshift PREPARE, EXECUTE and DEALLOCATE Example

Below example demonstrates the usage of parametrized PREPARE SELECT statement:

PREPARE prep_select (int) 
AS select * from test_table1 where col11 = $1;
EXECUTE prep_select (2);
DEALLOCATE prep_select;

In this example, a prepared statement prep_select is created and executed with the EXECUTE statement, using a parameter $1 that is passed in when the statement is executed. After the statement has been executed, the DEALLOCATE statement is used to deallocate the prepared statement, freeing the resources associated with it.

Here is the output of above dynamic SQL:

dev=# PREPARE prep_select (int) AS select * from test_table1 where col1 = $1;
PREPARE
dev=# EXECUTE prep_select (2);
 col1 | col2
------+------
    2 | BB
(1 row)

dev=# DEALLOCATE prep_select;
DEALLOCATE
dev=#

Redshift Dynamic SQL in Stored Procedures

Redshift stored procedure supports EXECUTE statement. The EXECUTE statement allows you to dynamically execute an SQL command within the stored procedure.

Following is the Amazon Redshift dynamic SQL example that usage of EXECUTE command inside Redshift stored procedure to execute SQL query dynamically.

CREATE PROCEDURE drop_table_if_exists(tmp_name INOUT varchar(256)) 
AS $$
DECLARE
  row record;
BEGIN
  EXECUTE 'drop table if exists ' || tmp_name;
  RAISE INFO 'Dynamic SQL executed!';
END;
$$ LANGUAGE plpgsql;

Following is the output:

dev=# CALL drop_table_if_exists('TMP');
INFO:  Table "tmp" does not exist and will be skipped
INFO:  Dynamic SQL executed!
 tmp_name
----------
 TMP
(1 row)

Best Practices for Redshift Dynamic SQL

Following are the some of best practices for using dynamic SQL in your SQL programming:

You should use dynamic SQL in cases where static SQL does not support the operation you want to perform.
Use dynamic SQL in cases where you do not know the exact SQL statements that must be executed by a Redshift stored procedure.
You can use dynamic SQL when full text of SQL statement is not known until runtime.
Use dynamic SQL when you are referencing a database objects that do not exist at compilation time.
The dynamic SQL queries are occasionally used in Redshift query optimization techniques to improve the performance of the query.
Use parameterized queries to build dynamic SQL.

In conclusion, you can use stored procedures, dynamic SQL, and other programmatic approaches to accomplish dynamic SQL operations. However, it’s important to keep in mind that dynamic SQL can also introduce security risks, as it can expose your database to SQL injection attacks if not handled properly. When you consider dynamic SQL vs static SQL in Redshift, both have pros and cons. You can use the dynamic SQL as per your requirements.

Hope this helps 🙂