Intro to Control Structures: IF, CASE, RETURN

Control structures are the backbone of any program, whether it's an SQL script, Python code, or even a Mars rover deciding if it should ping home or look for life around the next crater corner.

In PL/pgSQL, control structures help us:

• Do certain things if conditions are true.
• Control the order your code runs in.
• Build decision-making logic.

You can think of them like road signs: "If you see STOP, stop. If you see green light — go!"

The most popular control structures in PL/pgSQL:

• IF – checks a condition and runs the right commands.
• CASE – an alternative to IF when you have a bunch of conditions.
• RETURN – ends the function and gives back a result.

You probably already have some idea about them. And honestly, after learning other programming languages, these are gonna be a breeze.

The IF Conditional Operator

The IF operator is used to run code only if a certain condition is TRUE. Here’s the basic structure:

IF condition THEN
  -- Code that runs if the condition is true.
ELSE
  -- Alternative code if the condition is false.
END IF;

Let’s write a function that takes a number and returns whether it’s even or odd:

CREATE OR REPLACE FUNCTION check_even_odd(num INTEGER)
RETURNS TEXT AS $$
BEGIN
  IF num % 2 = 0 THEN
    RETURN 'Even number';
  ELSE
    RETURN 'Odd number';
  END IF;
END;
$$ LANGUAGE plpgsql;

Now let’s test it:

SELECT check_even_odd(4); -- Even number
SELECT check_even_odd(7); -- Odd number

Quick note: IF num % 2 = 0 THEN checks if the number divides by 2 with no remainder.

The ELSIF Condition

Sometimes one check isn’t enough, and you need to check a few different things. That’s what ELSIF is for.

Let’s say we have a function that returns a text comment about the temperature:

CREATE OR REPLACE FUNCTION temperature_comment(temp INTEGER)
RETURNS TEXT AS $$
BEGIN
  IF temp < 0 THEN
    RETURN 'Super cold!';
  ELSIF temp >= 0 AND temp <= 20 THEN
    RETURN 'Chilly';
  ELSE
    RETURN 'Warm!';
  END IF;
END;
$$ LANGUAGE plpgsql;

Let’s test it:

SELECT temperature_comment(-5);  -- Super cold!
SELECT temperature_comment(15);  -- Chilly
SELECT temperature_comment(25);  -- Warm!

CASE Syntax

If you’ve got a ton of conditions, the CASE operator is a lifesaver. It’s kinda like IF, but more "elegant".

CASE
  WHEN condition_1 THEN value_1
  WHEN condition_2 THEN value_2
  ELSE default_value
END;

Let’s make a function that returns a text grade based on a number:

CREATE OR REPLACE FUNCTION grade_comment(score INTEGER)
RETURNS TEXT AS $$
BEGIN
  RETURN CASE
    WHEN score >= 90 THEN 'Excellent'
    WHEN score >= 75 THEN 'Good'
    WHEN score >= 50 THEN 'Satisfactory'
    ELSE 'Unsatisfactory'
  END;
END;
$$ LANGUAGE plpgsql;

Let’s test it:

SELECT grade_comment(95); -- Excellent
SELECT grade_comment(80); -- Good
SELECT grade_comment(45); -- Unsatisfactory

Note: unlike IF, CASE returns a value, so you can use RETURN right inside CASE.

The RETURN Operator

RETURN ends the function and gives back a value. This is super important for PL/pgSQL functions, which have to return something (like text, a number, etc.).

Let’s see how RETURN works in a simple example:

CREATE OR REPLACE FUNCTION return_example()
RETURNS TEXT AS $$
BEGIN
  RETURN 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

Result:

SELECT return_example(); -- Hello, World!

But what if we have a few code branches? Like, checking input. Then RETURN will be used in different spots in the function:

CREATE OR REPLACE FUNCTION check_positive_negative(num INTEGER)
RETURNS TEXT AS $$
BEGIN
  IF num > 0 THEN
    RETURN 'Positive number';
  ELSIF num = 0 THEN
    RETURN 'Zero';
  ELSE
    RETURN 'Negative number';
  END IF;
END;
$$ LANGUAGE plpgsql;

Let’s test it:

SELECT check_positive_negative(10); -- Positive number
SELECT check_positive_negative(0);  -- Zero
SELECT check_positive_negative(-5); -- Negative number

Common Mistakes and Where You Can Get Tripped Up

1. Forgetting the END operator: forget END CASE; or END IF; and PostgreSQL will instantly remind you to close your code block.
2. Logic mistakes with ELSIF: order of conditions matters! A more general condition (temp > 0) shouldn’t come before a more specific one (temp > 20).
3. Forgot about RETURN: in PL/pgSQL, a function has to return something if you declared RETURNS.

That’s it! We covered the basics of control structures and logic in PL/pgSQL. You’ll use this stuff all the time when building more complex functions! Next time, we’ll check out loops and how to use them.