IF-ELSIF-ELSE control flow in PostgreSQL

Introduction

We use IF-ELSIF-ELSE to make decisions in our database code. It helps choose what to do based on different conditions.

When you want to check if a number is positive, negative, or zero and act differently.

When you want to assign a category to a product based on its price.

When you want to return different messages depending on a user's age.

When you want to update a status based on multiple conditions.

When you want to handle different cases in a stored procedure or function.

Syntax

PostgreSQL

IF condition THEN
    -- statements
ELSIF another_condition THEN
    -- statements
ELSE
    -- statements
END IF;

Use IF to start the decision, ELSIF for extra checks, and ELSE for the default case.

Each condition must be a true/false test.

Examples

This example assigns a grade based on the score.

PostgreSQL

IF score >= 90 THEN
    grade := 'A';
ELSIF score >= 80 THEN
    grade := 'B';
ELSE
    grade := 'C';
END IF;

This example sets stock status based on quantity.

PostgreSQL

IF quantity = 0 THEN
    status := 'Out of stock';
ELSIF quantity < 10 THEN
    status := 'Low stock';
ELSE
    status := 'In stock';
END IF;

Sample Program

This code checks the age and prints the category using IF-ELSIF-ELSE.

PostgreSQL

DO $$
DECLARE
    age INTEGER := 25;
    category TEXT;
BEGIN
    IF age < 13 THEN
        category := 'Child';
    ELSIF age < 20 THEN
        category := 'Teenager';
    ELSE
        category := 'Adult';
    END IF;
    RAISE NOTICE 'Category: %', category;
END $$;

Important Notes

Remember to end the IF block with END IF; in PostgreSQL.

ELSIF is used for additional conditions, not ELSE IF.

Use RAISE NOTICE to print messages inside DO blocks or functions.

Summary

IF-ELSIF-ELSE helps make choices based on conditions.

Use IF for the first test, ELSIF for more tests, and ELSE for the default.

Always close with END IF; in PostgreSQL.