Introduction to PostgreSQL IF Statement
Using the 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; these statements are 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 code only when some condition is fulfilled. In this article, we will learn about the general syntax of 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 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, then the statements in the if block is simply skipped. This is the syntax of 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 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 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 needs to execute the following statements below it. Then comes the declaration part, where we declare our variable named age and initialize it to 23 integer value. Then we begin or execution part where we will place our if statement in which we will check whether age is greater than 18; if so, the raise a notice saying, “You are eligible to vote!”.
Example #2
Now, let us initialize our variable to a value less than 19, for example, 15 and see that 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 value of the age for which you want to check eligibility. The output after copying and pasting the above statements on the psql command prompt terminal 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 if statement in it. We have to retrieve all the even numbers between the two numbers that are 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 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
If the statement is the simplest form of conditional statement that can be used to determine condition based on which the statements can be executed when the condition will evaluate to true. If the statement can be used in, do statements, functions or stored procedures, and loops and query statements for any expression mentioned in the condition that evaluates to a boolean result.
Recommended Articles
This is a guide to PostgreSQL IF Statement. Here we discuss an introduction to PostgreSQL IF Statement along with syntax, How does it work and examples with code and output. You can also go through our other related articles to learn more –
