Redshift RSQL Control Statements

Amazon Redshift is a data warehousing service provided by Amazon Web Services (AWS). It allows users to store and analyze large amounts of data in a scalable and cost-effective manner. Amazon AWS Redshift RSQL is a command-line client for interacting with Amazon Redshift clusters and databases. Redshift RSQL is similar to Teradata BTEQ and is used to interact with the data stored in a Redshift cluster. In this article, we will check Amazon Redshift RSQL control statements such as IF, ELSE, GOT, LABEL,etc.

Amazon Redshift RSQL control statements are used to control the flow of execution in a SQL program. Based on certain conditions, you can control the flow of execution. For example, exit the execution if an error occurs or jump to different query if error.

Following are the control statements available in Redshift RSQL:

IF: IF – ELSEIF – ELSE – ENDIF
GOTO
LABEL

Now, let us check these control statements with some examples.

Redshift RSQL IF Control Statement

The \IF statement and its related statements such as \ELSE, \ELSEIF, \ENDIF conditionally executes the portion of the input script. It is an extension of the PSQL \if and its related statements such as \elif, \else, \endif.

Redshift RSQL \IF and \ELSEIF support Boolean expressions including AND, OR and NOT conditions.

Following is an example of using \IF control statement in Redshift RSQL.

(test) vithal@dev=# \! cat test_input_if_else.sql
SELECT query FROM stv_inflight LIMIT 1 \gset
select :query as query;
\if :query > 1000000
    \remark 'Query id is greater than 1000000'
\elseif :query = 1000000
    \remark 'Query id is equal than 1000000'
\else
    \remark 'Query id is less than 1000000'
\endif

Redshift RSQL \GOTO Statement

Redshift RSQL \GOTO is a new command. It is similar to Teradata GOTO statements. The \GOTO skips all intervening SQL commands until a specified \LABEL is encountered. The \LABEL must be a forward reference. You cannot jump to a \LABEL that lexically precedes the \GOTO.

The following is the sample example.

(test) vithal@dev=# \! cat test_input_goto.sql
select count(*) as cnt from lineitem \gset
select :cnt as cnt;
\if :cnt > 100
    \goto LABELB
\endif

\label LABELA
\remark 'this is label LABELA'
\label LABELB
\remark 'this is label LABELB'

Redshift RSQL \LABEL Statement

Redshift RSQL \LABEL is a new command. It is similar to Teradata label statements. This statement identifies the point at which execution resumes processing, as specified in a previous \GOTO command.

The following is the sample example.

(test) vithal@dev=# \! cat test_input_goto.sql
select count(*) as cnt from lineitem \gset
select :cnt as cnt;
\if :cnt > 100
    \goto LABELB
\endif

\label LABELA
\remark 'this is label LABELA'
\label LABELB
\remark 'this is label LABELB'

Capabilities of Amazon Redshift RSQL

Amazon Redshift RSQL supports the capabilities of the PostgreSQL psql command-line tool with an additional set of capabilities specific to Amazon Redshift.

Supports enhanced control statements such as IF (\ELSEIF, \ELSE, \ENDIF), \GOTO and \LABEL.
Redshift RSQL supports single sign-on authentication using ADFS, PingIdentity, Okta, Azure ADm or other SAML/JWT based identity providers.
You can use Amazon Redshift RSQL to replace existing self-managed, on-premises data warehouse, extract, transform, load (ETL) and automation scripts, such as Teradata BTEQ scripts.
Amazon Redshift RSQL is available for Linux, Windows, and macOS X operating systems.
Redshift RSQL support Batch mode, which runs a script passed as an input parameter