How to work with control structures in PostgreSQL stored procedures: Using IF, CASE, and LOOP statements

 

Stored procedures in PostgreSQL are ones that define a function for creating triggers or custom functions. There are three main types of control structures available with PostgreSQL to use with stored procedures: IF, CASE, and LOOP.

IF statements 

1. Simple IF statements

 

Syntax 

IF condition THEN

   statement;

END IF;

The IF condition runs when a condition is evaluated as true. If the condition is false then it goes to the next statement after END IF.

 

Example

DO $$

DECLARE

  x integer := 10;

  y integer := 20;

BEGIN 

  IF x > y THEN

   RAISE NOTICE 'x is greater than y';

  END IF;



  IF x < y THEN

   RAISE NOTICE 'x is less than y';

  END IF;



  IF x = y THEN

   RAISE NOTICE 'x is equal to y';

  END IF;

END $$;

 

The above example declares 2 variables at the start. The x and y have been assigned a value. That is, x=10 and y=20. 

Next, there are three conditions: when x is greater than y, x is less than y, and x is equal to y. Depending upon the condition satisfied it will raise a notice—in this case, “x is less than y.”

 

2. IF-THEN-ELSE statements

Syntax

IF condition THEN

  statements;

ELSE

  additional statements;

END IF;

 

Example 

DO $$

DECLARE

  x integer := 10;

  y integer := 20;

BEGIN 

   IF x > y THEN 

      RAISE NOTICE 'x is greater than y';

   ELSE

      RAISE NOTICE 'x is not greater than y';

   END IF;

END $$;

 

As in the first example, here 2 variables are declared at the start: x=10 and y=20. 

Next, there is one condition: when x is greater than y it raises notice that  “x is greater than y.” When that condition is not met, it raises the notice  “x is not greater than y.” In this case, the condition is not met, so the ELSE clause is executed and the output for the ELSE part is printed.

 

CASE statements

The CASE statement uses IF-THEN-ELSE logic within a single statement. It facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement and applying it to many possible conditions.

 

Syntax

CASE condition 

WHEN condition value THEN statement

ELSE additional statement; 

 

Example 

We can use CASE to evaluate multiple conditions for a single variable, “job_id.” If “job_id” is “ACCOUNT,” the salary increase is 10%; if “job_id” is “IT_PROG,” the salary increase is 15%; if “job_id” is “SALES,” the salary increase is 20%. For all other job roles, there is no increase in salary.

SELECT last_name, job_id, salary,

CASE job_id 

WHEN 'ACCOUNT' THEN 1.10*salary 

WHEN 'IT_PROG' THEN 1.15*salary

WHEN 'SALES' THEN 1.20*salary

ELSE salary END "REVISED_SALARY" FROM employees;

 

LOOP statements

Syntax

LOOP

<execution block starts>

<EXIT condition based on requirement> 

<execution_block_ends>

END LOOP;

The LOOP keyword declares the beginning of the loop, and END LOOP declares the end of the loop.  The execution block contains all the code that needs to be executed, including the EXIT condition.

 

Example

 

DO $$

DECLARE 

a NUMBER:=1; 

BEGIN

RAISE NOTICE 'Loop started.';

LOOP

RAISE NOTICE 'a';

a:=a+1;

EXIT WHEN a>5;

END LOOP;

RAISE NOTICE 'Loop completed';

END;

END $$;