PostgreSQL IF – Elsif – Else syntax

In PostgreSQL, the IF-ELSIF-ELSE conditional statement allows you to execute different blocks of code based on certain conditions. This conditional statement is often used in PL/pgSQL, which is the procedural language supported by PostgreSQL. PL/pgSQL provides control structures like IF-ELSIF-ELSE to enhance the functionality of database functions and stored procedures.

Syntax

The basic syntax of the IF-ELSIF-ELSE conditional statement in PL/pgSQL is as follows:

IF condition1 THEN
   -- code block executed if condition1 is true
ELSIF condition2 THEN
   -- code block executed if condition2 is true
...
ELSE
   -- code block executed if none of the conditions are true
END IF;

Here’s a step-by-step explanation of how this statement works:

The IF keyword starts the conditional statement block.
After the IF keyword, you specify a condition that evaluates to either true or false.
If the condition is true, the code block following the condition is executed. This code block can contain one or more SQL statements or procedural code.
If the condition is false, the statement moves to the next condition specified using the ELSIF keyword.
Each ELSIF condition is evaluated in order. If any of the ELSIF conditions are true, the code block following that condition is executed.
If none of the conditions specified in the IF or ELSIF clauses are true, the statement moves to the ELSE block.
The ELSE block is optional. If it is present, the code block following the ELSE keyword is executed when none of the conditions are true.
The END IF; statement marks the end of the conditional statement block.

Example

Here’s an example to demonstrate the usage of the IF-ELSIF-ELSE conditional statement in PL/pgSQL:

CREATE OR REPLACE FUNCTION check_grade(score integer) RETURNS text AS $$
DECLARE
    grade text;
BEGIN
    IF score >= 90 THEN
        grade := 'A';
    ELSIF score >= 80 THEN
        grade := 'B';
    ELSIF score >= 70 THEN
        grade := 'C';
    ELSE
        grade := 'D';
    END IF;
    
    RETURN grade;
END;
$$ LANGUAGE plpgsql;

In this example, we define a PL/pgSQL function called check_grade that accepts a score as an input parameter. The function uses the IF-ELSIF-ELSE conditional statement to determine the grade based on the score. If the score is greater than or equal to 90, it assigns ‘A’ to the grade variable. If the score is between 80 and 89, it assigns ‘B’. If the score is between 70 and 79, it assigns ‘C’. For any score below 70, it assigns ‘D’. Finally, the function returns the grade.

You can then call the check_grade function with a score as an argument to get the corresponding grade:

SELECT check_grade(85);  -- Output: B

In this case, the function returns ‘B’ because the score provided (85) falls within the range of 80-89.