PL/pgSQL – IF Statement

In PostgreSQL’s PL/pgSQL, you can use the IF statement to conditionally execute a block of code based on a specified condition. The IF statement allows you to branch your code flow, executing different code blocks depending on whether the condition is true or false.

Here’s the basic syntax of the IF statement:

IF condition THEN 
    -- Code to execute if the condition is true 
ELSIF other_condition THEN 
    -- Code to execute if another condition is true (optional) 
ELSE 
    -- Code to execute if none of the conditions are true (optional) 
END IF;

where

• condition is the boolean expression or condition that you want to evaluate. If it evaluates to TRUE, the code block following THEN will be executed. If it evaluates to FALSE, the code block will be skipped.
• ELSIF other_condition is an optional part that allows you to specify additional conditions to check. If the previous IF or ELSIF conditions are not true, PostgreSQL will evaluate this condition. You can have multiple ELSIF blocks if needed.
• ELSE is also optional. If none of the previous conditions are true, the code block following ELSE will be executed. You can only have one ELSE block, and it comes at the end.
• END IF; marks the end of the IF block.

Here’s an example of a PL/pgSQL function that uses the IF statement to check the value of a variable:

CREATE OR REPLACE FUNCTION check_value(x INT) 
RETURNS TEXT AS $$ 
DECLARE 
   result TEXT; 
BEGIN 
   IF x > 0 THEN 
      result := 'Positive'; 
   ELSIF x < 0 THEN 
      result := 'Negative'; 
   ELSE 
      result := 'Zero'; 
   END IF; 
   RETURN result; 
END; 
$$ LANGUAGE plpgsql;

In this example:

• The function check_value accepts an integer parameter x.
• The IF statement checks whether x is positive, negative, or zero, and assigns the appropriate value to the result variable.
• The function returns the result variable as text.

You can call this function and pass different values for x to see how the IF statement works:

SELECT check_value(5); -- Returns 'Positive' 
SELECT check_value(-2); -- Returns 'Negative' 
SELECT check_value(0); -- Returns 'Zero'

The IF statement is a fundamental control structure in PL/pgSQL and is commonly used for making decisions and controlling the flow of your code based on conditions.