This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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:
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';
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';
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';
![]() | 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 . |
 
Continue to: