Redshift Conditional Statements

The Redshift SQL conditional statements are a useful and important part of the plpgsql language. You can use Redshift control structures to perform some critical decisions based on data and manipulate SQL data in a flexible and powerful way. In Redshift, you can use conditional statements to control the flow of execution of a SQL script based on conditions. You can use these Redshift conditional statements for data manipulation in your complex SQL queries.

Redshift pgplsql Conditional Statements - IF, LOOP, WHILE, FOR, CONTINUE and EXIT

The Redshift conditional control flow statements are categorized into two parts:

Conditional control structures
Iterative control structures.

Redshift Conditional Control

The IF statements can perform particular task based on the certain conditions. There are four forms of IF statements available in Redshift supported plpgsql:

IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF
IF-THEN-ELSIF-THEN-ELSE

Every plpgsql IF statement should have the corresponding END IF statements. In case there is nested IF then there should be two END IF, one for main IF and other one for nested IF.

Redshift IF-THEN Statements

The IF-THEN statements are simplest form of IF statements. If the condition is true statements between THEN and END IF are executed, otherwise statements following END IF are executed.

For example:

IF v_id IS NOT NULL 
THEN 
  UPDATE patient_dim SET mobile= v_mobile where id = v_id; 
END IF;

Redshift IF-THEN-ELSE statements

This conditional control works same as in other programming language. IF-THEN-ELSE statements has an ELSE branch to evaluate in cases when condition evaluates to false results. You can write the statements to run in the ELSE part.

For example:

IF v_id IS NOT NULL THEN
 UPDATE patient_dim SET mobile = v_mobile where id = v_id;
 return 1;
ELSE
 return 0;
END IF;

Here is the Redshift stored procedure example to demonstrate IF-THEN-ELSE condition statement.

CREATE OR REPLACE PROCEDURE if_else_example()
LANGUAGE plpgsql
AS $$
DECLARE
  id INTEGER;
  flag char(10);
BEGIN
  id := 0;

  IF id = '1' 
  THEN 
	flag := 'true'; 
  ELSE
	flag := 'false'; 
  END IF;

  RAISE INFO 'Flag = %', flag;
  
END;
$$;

Here is the output:

CALL if_else_example();

INFO:  Flag = false

Redshift IF-THEN-ELSIF statements

This condition is the nesting of IF in the ELSE part of IF-THEN-ELSE statements.

For example:

IF v_flag = '1' 
THEN 
  Flag := 'true'; 
ELSIF v_flag = '0' 
THEN 
  Flag := 'false'; 
END IF;

Redshift IF-THEN-ELSIF-ELSE statements

This condition is the nesting of IF with ELSE statement in the ELSE part of IF-THEN-ELSIF-ELSE statements.

For example:

IF v_flag = '1' 
THEN 
  Flag := 'true'; 
ELSIF v_flag = '0' 
THEN 
  Flag := 'false'; 
ELSE 
  Flag := NOT FOUND'; 
END IF;

This condition provides more flexibility to test multiple conditions.

Redshift CASE Statement

The case statement is one of the conditional statements in Redshift database. You can use the CASE statement in Redshift to evaluate a list of conditions and return a result expression corresponding to the first true condition.

Following is the basic syntax of the CASE statement in Redshift:

CASE
  WHEN case_condition_1 THEN result_1
  WHEN case_condition_2 THEN result_2
  ...
  ELSE default_result
END

Following is the example of CASE statement in Amazon Redshift.

SELECT column_1,
       CASE
         WHEN id = 'One' THEN 1
         WHEN id= 'Two' THEN 2

         ELSE 0
       END AS case_out
FROM table_name;

Redshift Iterative Control

With the help of iterative control statements, you can control the flow of execution in your plpgsql statements.

Redshift supports following iterative control structures:

LOOP
WHILE
FOR
CONTINUE
EXIT

Redshift LOOP, EXIT, WHEN Statements

The LOOP statement defines an unconditional loop that repeats until terminated by an EXIT or a RETURN statement. The optional label can be used by EXIT and CONTINUE statements within nested loops to specify which loop the EXIT and CONTINUE statements refer to.

[<<label>>]
LOOP
  statements
END LOOP [ label ];

For example, consider following stored procedure with simple loop statement with EXIT and CONTINUE labels.

CREATE OR REPLACE PROCEDURE redshift_simple_loop()
LANGUAGE plpgsql
AS $$
DECLARE 
	cnt INTEGER := 0;
BEGIN
  <<simple_loop_exit_continue>>
  LOOP
	RAISE INFO 'Count: %', cnt;
	cnt = cnt + 1;
    EXIT simple_loop_exit_continue WHEN (cnt > 10);
  END LOOP;
  RAISE INFO 'Loop Statement Executed!!!';
END;
$$;

Here is the output of above stored procedure:

CALL redshift_simple_loop();

INFO:  Count: 0
INFO:  Count: 1
INFO:  Count: 2
INFO:  Count: 3
INFO:  Count: 4
INFO:  Count: 5
INFO:  Count: 6
INFO:  Count: 7
INFO:  Count: 8
INFO:  Count: 9
INFO:  Count: 10
INFO:  Loop Statement Executed!!!

If you have multiple loop statements, you can jump between them using CONTINUE statement.

CONTINUE label;

For example,

CONTINUE simple_loop_continue_test WHEN (cnt > 10);

Redshift WHILE Loop Statement

With the use of Redshift WHILE statement, you can loop through a sequence of statements until the evaluation of the condition expression is true. The expression is checked just before each entry to the loop body.

[<<label>>]
WHILE expression LOOP
  statements
END LOOP [ label ];

For example, consider below stored procedure to verify WHILE loop:

CREATE OR REPLACE PROCEDURE redshift_simple_while()
LANGUAGE plpgsql
AS $$
DECLARE 
	cnt INTEGER := 0;
BEGIN
  WHILE cnt <= 10 
  LOOP 
	RAISE INFO 'Count: %', cnt;
	cnt = cnt + 1;
  END LOOP;
  RAISE INFO 'Loop Statement Executed!!!';
END;
$$;

Here is the output of while loop SP:

CALL redshift_simple_while();

INFO:  Count: 0
INFO:  Count: 1
INFO:  Count: 2
INFO:  Count: 3
INFO:  Count: 4
INFO:  Count: 5
INFO:  Count: 6
INFO:  Count: 7
INFO:  Count: 8
INFO:  Count: 9
INFO:  Count: 10
INFO:  Loop Statement Executed!!!

Redshift FOR Loop statement

With the help of FOR statements, you can loop over the range of integer values.

[<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
  statements
END LOOP [ label ];

For example, consider below stored procedure to verify FOR loop:

CREATE OR REPLACE PROCEDURE redshift_simple_for()
LANGUAGE plpgsql
AS $$
DECLARE 
	cnt INTEGER := 0;
BEGIN
  FOR cnt IN 1..5 LOOP
	RAISE INFO 'Count: %', cnt;
  END LOOP;
  RAISE INFO 'For Loop Statement Executed!!!';

  FOR cnt IN REVERSE 5..1 LOOP
	RAISE INFO 'Count: %', cnt;
  END LOOP;
  RAISE INFO 'For Loop Statement Executed in Reverse order!!!';
END;
$$;

Here is the output:

INFO:  Count: 1
INFO:  Count: 2
INFO:  Count: 3
INFO:  Count: 4
INFO:  Count: 5
INFO:  For Loop Statement Executed!!!
INFO:  Count: 5
INFO:  Count: 4
INFO:  Count: 3
INFO:  Count: 2
INFO:  Count: 1
INFO:  For Loop Statement Executed in Reverse order!!!

Hope this helps 🙂