IF statements allow the programmer to specify an action to be taken when a condition occurs. There are three types of IF statements. They are:

IF THEN

This statement specifies that if the condition is true, then it executes the action. If the condition is false, it will jump out of the block and execute the statement after the END IF .

The syntax to use this in a block is:

      DECLARE

      BEGIN
        IF condition THEN
           action_statement;
        END IF;
      END;
      

For example, this function multiplies together numbers less than or equal to 10. If any of the numbers supplied is greater than 10, it displays a 0 as the result:

Example 9-20. PL/pgSQL Function Using If

      CREATE FUNCTION mult_lessthan10(integer, integer) RETURNS integer AS'
       DECLARE
         -- defines alias names 
         -- for the two input numbers
         num1 ALIAS FOR $1;
         num2 ALIAS FOR $2;
          
       BEGIN
         -- if any of the numbers are less than 10          
         IF num1 <=10 AND num2 <=10 THEN
            -- multiply them together
            return (num1 * num2);
         -- end of the if body statement
         END IF;

       -- if less than 10, return 0 
       -- because it is defined to return an integer
       return 0;

       END; 
       ' LANGUAGE 'plpgsql';
      

IF THEN ELSE

This specifies that a statement should be executed if the condition evaluates to TRUE . Another statement is executed when the condition evaluates to FALSE .

The syntax to use this is:

      DECLARE

      BEGIN

        IF condition THEN
           action_statement;
        ELSE
           action_statement;
        END IF;

      END;
      

For example, this function multiplies numbers less than 10 and adds together numbers greater than 10:

Example 9-21. PL/pgSQL Function Using IF ELSE

      CREATE FUNCTION add_multiply(integer, integer) 
       RETURNS integer AS'

       DECLARE 
         -- defines two alias names 
         -- for the input numbers
         num1 ALIAS FOR $1;
         num2 ALIAS FOR $2;          

       BEGIN
         -- if any of the numbers are less than 10
         IF num1 <=10 AND num2 <=10 THEN

            -- add the input numbers together
            return (num1 + num2);                 

         ELSE
            -- if greater than 10 
            -- multiply the input numbers together
            return (num1 * num2);

         -- end of the if body statement
         END IF;

       END; 
       ' LANGUAGE 'plpgsql';
      

IF THEN ELSE IF

This nests an IF statement inside of the ELSE statement. Therefore, this specifies that in a certain situation, execute the first statement. If a different situation occurs and it matches the ELSE IF condition, then execute the statement in the ELSE IF body. This requires two END IFs to end the block. The inner END IF closes the ELSE IF body and the outer END IF closes the IF body.

The syntax to use the IF THEN ELSE IF is:

      DECLARE

      BEGIN
        IF condition THEN
           action_statement;
        ELSE IF condition THEN
           action_statement;
           END IF; 
        END IF;
      END;
      

For example, this function takes in two numbers and multiples the numbers less than 10. Numbers greater than 10 and less than 50 are added together. Any numbers above the 50 range are not computed and it will simply return a zero:

Example 9-22. PL/pgSQL Function Using ELSE IF

      CREATE FUNCTION mult_add(integer, integer) RETURNS integer AS'
       DECLARE
         -- defines alias names 
         -- for the two input numbers
         num1 ALIAS FOR $1;
         num2 ALIAS FOR $2;        

       BEGIN  
         -- if any of the numbers are less than 10
         IF num1 < 10 AND num2 < 10 THEN

             -- multiply them together
             return num1 * num2;

           -- if any of the numbers are between 10 and 50
           ELSE IF num1 < 50 AND num2 < 50 THEN
            
             -- add them together
             return num1 + num2;
            
           -- end of the inner if body statement
           END IF;
            
         -- end of the outer if body statement
         END IF;

         -- displays 0 because it is defined 
         -- to return an integer 
         return 0;

       END; 
       ' LANGUAGE 'plpgsql';
      

Note Number of END IFs
 

Although the structure of each if statement is different, this rule applies to every type of IF statement: ALL beginning if statements need a correspond END IF .