Updated May 9, 2023
Introduction to PostgreSQL IF Statement
Using queries that will give control of the database and allow the user to manipulate it effectively and strongly are provided in any SQL or database language. Some statements help the user have better control over the queries and help in decision-making based on PostgreSQL conditions, called the control statements. One of the most crucial and powerful out of all of them is the if statement. This statement allows us to execute certain codes only when conditions are fulfilled. In this article, we will learn about the general syntax of the if statement in PostgreSQL and understand its practical usage with the help of some examples.
Syntax
IF condition THEN
-- code or statements to be executed END IF;Explanation: The condition is the boolean expression that evaluates to true or false. This condition can be any expression ranging from variable comparison to query result or isNull statement or exists statement that returns a boolean value; if this condition evaluates to true, then the code or statements you want to execute are specified after the THEN keyword is executed. The body of the if block starts from the THEN keyword till the END IF statement. If the condition doesn’t evaluate to true, the statements in the if block are skipped. This is the syntax of the if statement in psql.
How does PostgreSQL IF Statement work?
Usage: We can use this if statement in functions, stored procedures, the sequence of statements inside the DO statement, or with loops such as simple LOOP statement, for loop, while loop, etc. using expressions with declared variables, numbers, string comparisons, query results, and any functions that return boolean value like isnull, exists and many other.
Examples to Implement PostgreSQL IF Statement
Let us consider a simple example where we will declare one variable named age. If the age is greater than 18, then we will raise a message in the form of a notice saying that “he/she is eligible to vote”. Let us see how we can perform this execution with the help of the do statement.
Example #1
Code:
DO $$ DECLARE
age integer := 23;
BEGIN
IF age > 18 THEN
RAISE NOTICE 'You are eligible to vote!';
END IF;
END $$;Output:
Explanation: The DO statement specifies that Postgres must execute the following statements below. Then comes the declaration part, where we declare our variable named age and initialize it to 23 integer values. Then we begin or execution part, where we will place our if statement in which we will check whether the age is greater than 18; if so, the raise a notice saying, “You are eligible to vote!”.
Example #2
Let us initialize our variable to a value less than 19, for example, 15, and see that it is the output. Then the output is seen somewhat like the following :
Code:
DO $$ DECLARE
age integer := 15;
BEGIN
IF age > 18 THEN
RAISE NOTICE 'You are eligible to vote!';
END IF;
END $$;Output:
Explanation: No notice is raised, and the statements in the if block is simply skipped for execution.
Example #3
Let us write a function and try to call them by passing the age variable instead of statically declaring and initializing in the above example:
Code:
CREATE OR REPLACE FUNCTION iseligible(int) RETURNS void AS $$ DECLARE
age int:=$1;
BEGIN
IF age > 18 THEN
RAISE NOTICE 'You are eligible to vote as your age is %!', age; END IF;
END;
$$ LANGUAGE plpgsql;Output:
Explanation: This will create a function named iseligible. We can call this function whenever we want to pass the age value for which you want to check eligibility. After copying and pasting the above statements on the psql command prompt terminal, the output is as follows.
Example #4
Now, we want to check the eligibility for a 21-year-old guy; then we can call our function in the following way:
Code:
SELECT iseligible(21);Output:
Explanation: This displays the notice along with our message, which means the condition inside the if-statement evaluated to true and if the block’s body was executed.
Example #5
Now, let us try with a value less than 19, take 15. Then our calling statement will be:
Code:
SELECT iseligible(15);Output :
Example #6
Let us take one example where we will take a for loop and use an if statement in it. We must retrieve all the even numbers between the two numbers passed to our function and print them. Our function will then be like this:
Code:
CREATE OR REPLACE FUNCTION displayEvenNumbers(int,int) RETURNS void AS $$ DECLARE
first int; last int:=$2; BEGIN
IF $1%2=0
THEN first=$1;
ELSE first=$1+1;
END IF;
FOR sampleCounter IN first..last BY 2 LOOP
RAISE NOTICE 'Even numbers : %', sampleCounter; END LOOP;
END;
$$ LANGUAGE plpgsql;Output:
The above function would give the following output if the function were created successfully.
Explanation: Here, the first parameter will be starting number, and the second will be the ending number between which we want even numbers. We will first check whether the starting number first is an even or odd number by using the modulus operator and if statement. If the first parameter is even, we will assign that value to the “first” variable; else, we will add one to the first parameter value and then assign that value to the “first” variable. Then we will use a for loop ranging from the “first” variable to the “last” variable to whom the value of the second parameter is assigned, and the step value will be 2 as all even numbers will have a difference of two between consecutive even values.
Example #7
Now we can call the displayevennumbers function to get the even numbers between 15 and 22 using the following query statement.
Code:
SELECT displayevennumbers(15,22);Output:
Conclusion
The if statement is the simplest type of conditional statement that allows you to specify a condition based on which statements will execute if the condition evaluates to true.You can use the statement in do statements, functions, or stored procedures, as well as loops and query statements, for any expression that evaluates to a boolean result.
Recommended Articles
We hope that this EDUCBA information on the “PostgreSQL IF Statement” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
