IF-ELSIF-ELSE control flow in PostgreSQL - Step-by-Step Execution

Concept Flow - IF-ELSIF-ELSE control flow

Start

↓

Check IF condition

Yes| No↓

Check ELSIF condition

Yes| No↓

Execute ELSE block

↓

End

The flow checks the IF condition first; if false, it checks ELSIF conditions in order; if none match, it executes ELSE block if present.

Execution Sample

PostgreSQL

DO $$
BEGIN
  IF 3 > 5 THEN
    RAISE NOTICE 'IF block';
  ELSIF 3 = 3 THEN
    RAISE NOTICE 'ELSIF block';
  ELSE
    RAISE NOTICE 'ELSE block';
  END IF;
END $$;

This code checks conditions in order and prints which block runs based on the first true condition.

Execution Table

Step	Condition Checked	Result	Branch Taken	Output
1	3 > 5	False	Skip IF block
2	3 = 3	True	Execute ELSIF block	ELSIF block
3	ELSE	Skipped	No
4	End IF	N/A	Exit control flow

💡 Condition 3 = 3 is True, so ELSIF block runs and control flow ends.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Condition Result	N/A	False	True	True
Output	None	None	'ELSIF block'	'ELSIF block'

Key Moments - 2 Insights

Why does the ELSE block not run even though IF is false?

Because the ELSIF condition is true (see execution_table row 2), the control flow executes the ELSIF block and skips ELSE.

What happens if all IF and ELSIF conditions are false?

The ELSE block runs as a fallback (not shown in this trace), ensuring some code executes if no conditions match.

Visual Quiz - 3 Questions

Look at the execution table, what is the result of the first condition checked?

A True

B False

C Skipped

D Error

💡 Hint

Check the 'Condition Checked' and 'Result' columns in row 1 of the execution_table.

At which step does the control flow decide which block to execute?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Branch Taken' and 'Output' columns in the execution_table to see when execution happens.

If the ELSIF condition was false, which block would run next?

A IF block

B ELSIF block again

C ELSE block

D No block runs

💡 Hint

Refer to the concept_flow and execution_table rows 2 and 3 to understand fallback behavior.

Concept Snapshot

IF-ELSIF-ELSE syntax:
IF condition THEN
  -- code
ELSIF condition THEN
  -- code
ELSE
  -- code
END IF;

Checks conditions in order; runs first true block; ELSE runs if none true.

Full Transcript

This example shows how PostgreSQL IF-ELSIF-ELSE control flow works. First, the IF condition is checked. If false, the ELSIF condition is checked. If ELSIF is true, its block runs and ELSE is skipped. If all conditions are false, ELSE runs. The execution table traces each step, showing condition results and which block executes. Variables track condition results and output messages. Key moments clarify why ELSE may not run and what happens if all conditions fail. The quiz tests understanding of condition results and flow decisions.

Practice

1.

What is the purpose of the ELSIF keyword in PostgreSQL's IF control flow?

easy

A. To test an additional condition if the previous IF condition is false

B. To end the IF statement

C. To execute code unconditionally

D. To start a loop inside the IF block

Solution

1. Step 1: Understand the role of IF and ELSIF

   The IF keyword tests the first condition. If it is false, ELSIF allows testing another condition.

2. Step 2: Differentiate ELSIF from other keywords

   ELSIF is not for ending or unconditional execution; it is for additional conditional checks.

3. Final Answer:

   To test an additional condition if the previous IF condition is false -> Option A

4. Quick Check:

   ELSIF = additional condition test [OK]

Hint: Remember: ELSIF adds more conditions after IF [OK]

Common Mistakes:

• Thinking ELSIF ends the IF block
• Confusing ELSIF with ELSE
• Using ELSIF without a preceding IF

2.

Which of the following is the correct syntax to close an IF statement in PostgreSQL?

easy

A. END;

B. ENDIF;

C. END IF;

D. FINISH IF;

Solution

1. Step 1: Recall PostgreSQL block ending syntax

   PostgreSQL requires END IF; to close an IF block explicitly.

2. Step 2: Compare options

   ENDIF; and FINISH IF; are invalid. END; alone closes other blocks but not IF.

3. Final Answer:

   END IF; -> Option C

4. Quick Check:

   Close IF with END IF; [OK]

Hint: Always end IF blocks with END IF; in PostgreSQL [OK]

Common Mistakes:

• Using END; alone to close IF
• Writing ENDIF; without space
• Forgetting to close IF blocks

3.

Consider this PostgreSQL code snippet inside a function:

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

If score is 85, what will be the value of result after execution?

medium

A. 'A'

B. 'F'

C. 'C'

D. 'B'

Solution

1. Step 1: Evaluate conditions in order for score = 85

   Check if 85 >= 90? No. Then check 85 >= 80? Yes.

2. Step 2: Assign result based on first true condition

   Since 85 >= 80 is true, result is set to 'B'. Remaining conditions are skipped.

3. Final Answer:

   'B' -> Option D

4. Quick Check:

   85 >= 80 = true, so result = 'B' [OK]

Hint: Check conditions top to bottom; first true sets the result [OK]

Common Mistakes:

• Choosing 'A' because 85 is close to 90
• Ignoring order of conditions
• Assigning 'C' or 'F' incorrectly

4.

Identify the error in this PostgreSQL IF block:

IF value > 10 THEN
  RAISE NOTICE 'Value is large';
ELSIF value < 5
  RAISE NOTICE 'Value is small';
ELSE
  RAISE NOTICE 'Value is medium';
END IF;

medium

A. Using ELSE without condition

B. Missing THEN after ELSIF value < 5

C. Incorrect use of RAISE NOTICE

D. Missing END IF;

Solution

1. Step 1: Check syntax of each condition

   The ELSIF line lacks the required THEN keyword after the condition.

2. Step 2: Confirm other parts are correct

   END IF; is present, RAISE NOTICE is valid, and ELSE does not take a condition.

3. Final Answer:

   Missing THEN after ELSIF value < 5 -> Option B

4. Quick Check:

   ELSIF must have THEN [OK]

Hint: Always write THEN after IF and ELSIF conditions [OK]

Common Mistakes:

• Omitting THEN after ELSIF
• Adding condition after ELSE
• Forgetting END IF;

5.

You want to write a PostgreSQL function that returns 'Positive', 'Negative', or 'Zero' based on an integer input num. Which IF-ELSIF-ELSE block correctly implements this logic?

-- Options:
A) IF num > 0 THEN RETURN 'Positive';
   ELSIF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

B) IF num > 0 THEN RETURN 'Positive';
   ELSEIF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

C) IF num > 0 THEN RETURN 'Positive';
   ELSIF num < 0 THEN RETURN 'Negative';
   ELSEIF num = 0 THEN RETURN 'Zero';
   END IF;

D) IF num > 0 THEN RETURN 'Positive';
   IF num < 0 THEN RETURN 'Negative';
   ELSE RETURN 'Zero';
   END IF;

hard

A. Correct use of IF, ELSIF, ELSE with proper syntax

B. Uses invalid keyword ELSEIF instead of ELSIF

C. Uses ELSEIF after ELSE which is invalid

D. Nested IF without closing first IF properly

Solution

1. Step 1: Check keywords and structure

   Correct use of IF, ELSIF, ELSE with proper syntax uses IF, ELSIF, and ELSE correctly with proper endings.

2. Step 2: Identify errors in other options

   Uses invalid keyword ELSEIF instead of ELSIF uses invalid ELSEIF. Uses ELSEIF after ELSE which is invalid uses ELSEIF after ELSE. Nested IF without closing first IF properly nests IF without closing properly.

3. Final Answer:

   Correct use of IF, ELSIF, ELSE with proper syntax -> Option A

4. Quick Check:

   Use IF, ELSIF, ELSE; no ELSEIF [OK]

Hint: Use ELSIF, not ELSEIF; close IF with END IF; [OK]

Common Mistakes:

• Using ELSEIF instead of ELSIF
• Placing ELSEIF after ELSE
• Improper nesting without END IF;