Summary: in this tutorial, we will show you how to use PL/pgSQL IF statements to execute a command based on a specific condition.
The IF statement is used to execute a command conditionally. PL/pgSQL provides you with three forms of the IF statements.
The simplest form of PL/pgSQL IF statement
The IF statement executes statements if a conditionis true. If the condition evaluates to false, the control is passed to the next statement after the END IF part. The following illustrates the simplest form of the IF statement:
1 2 3 | IF condition THEN statement; END IF; |
The condition is a boolean expression that evaluates to true or false.
The statement is a statement that will be executed if the condition is true. It can be any valid statement, even another IF statement. An IF code statement is placed inside another IF statement is called a nested-IF statement.
The following flowchart illustrates the simple IF statement.
See the following example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DO $$ DECLARE a integer := 10; b integer := 20; BEGIN IF a > b THEN RAISE NOTICE 'a is greater than b'; END IF; IF a < b THEN RAISE NOTICE 'a is less than b'; END IF; IF a = b THEN RAISE NOTICE 'a is equal to b'; END IF; END $$; |
In this example, we declare two variables a and b. In the body of the block, we compare the value of a and b using the comparison operator >, < and = in the boolean expressions of the IF statements and print out the corresponding message.
Because a is less than b, we receive the following output when we execute the block:
1 | NOTICE: a is less than b |
PL/pgSQL IF THEN ELSE statement
The IF THEN ELSE statement executes a command when the condition is true and it executes an alternative command when the condition is false. The following illustrates the syntax of the IF THEN ELSE statement:
1 2 3 4 5 | IF condition THEN statements; ELSE alternative-statements; END IF; |
The following flowchart illustrates the IF ELSE statement.
See the following example:
1 2 3 4 5 6 7 8 9 10 11 | DO $$ DECLARE a integer := 10; b integer := 20; BEGIN IF a > b THEN RAISE NOTICE 'a is greater than b'; ELSE RAISE NOTICE 'a is not greater than b'; END IF; END $$; |
Because the expression a > b is false, the statement in the ELSE clause is executed. We get the following output:
1 | NOTICE: a is not greater than b |
PL/pgSQL IF THEN ELSIF THEN ELSE statement
The final form of the IF statement is as follows:
1 2 3 4 5 6 7 8 9 10 | IF condition-1 THEN if-statement; ELSIF condition-2 THEN elsif-statement-2 ... ELSIF condition-n THEN elsif-statement-n; ELSE else-statement; END IF: |
The IF and IF THEN ELSE statements allow you to have just one condition to evaluate. However, with this form of the IF statement, you may have multiple conditions to evaluate.
The the condition is true, the corresponding statement in that branch is executed. For example, if the condition-1, condition-2, etc., is true then the corresponding statement: if-statement, elseif-statement-2, etc., will execute. In case one condition is true, PostgreSQL will stop evaluating the underneath conditions.
If all conditions are false, the statements in the last ELSE branch are executed.
The following flowchart illustrates the IF ELSIF ELSE statement.
Let’s take a look at the following example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | DO $$ DECLARE a integer := 10; b integer := 10; BEGIN IF a > b THEN RAISE NOTICE 'a is greater than b'; ELSIF a < b THEN RAISE NOTICE 'a is less than b'; ELSE RAISE NOTICE 'a is equal to b'; END IF; END $$; |
In this example, because a is equal to b, the statement in the ELSE branch is executed.
1 | NOTICE: a is equal to b |
In this tutorial, you have learned three forms of the IF statement to execute a statement based on a certain condition.