IntermediateBackend Notes

PL/pgSQL Control Flow: IF, CASE, and LOOP in a DO Block

Every PL/pgSQL block starts the same way — DO $$ ... $$ LANGUAGE plpgsql — and everything else is IF, CASE, or LOOP wearing different syntax.

Hen HeangJuly 11, 20266 min read

PostgreSQLSQLPL/pgSQL

PL/pgSQL is PostgreSQL's procedural extension to plain SQL — the thing you reach for when a query alone can't express the logic (branching, looping, reusable functions). Every anonymous block has the same shape.

The basic shape

DO $$
DECLARE
  message VARCHAR := 'hello world';
BEGIN
  RAISE NOTICE 'The message is %', message;
END;
$$ LANGUAGE plpgsql;

IF / ELSIF / ELSE

sql

DO $$
DECLARE
  a integer := 10;
  b integer := 10;
BEGIN
  IF a > b THEN
    RAISE NOTICE 'a is greater than b';
  ELSIF a < b THEN
    RAISE NOTICE 'a is less than b';
  ELSE
    RAISE NOTICE 'a is equal to b';
  END IF;
END $$;

CASE: simple vs searched

Simple CASE -- matches an exact value

DO $$
DECLARE x INTEGER := 1;
BEGIN
  CASE x
    WHEN 1, 2 THEN
      RAISE NOTICE 'one or two';
    ELSE
      RAISE NOTICE 'other value than one or two';
  END CASE;
END $$;

Searched CASE -- matches a boolean condition

DO $$
DECLARE x INTEGER := 8;
BEGIN
  CASE
    WHEN x BETWEEN 0 AND 10 THEN
      RAISE NOTICE 'value is between zero and ten';
    WHEN x BETWEEN 11 AND 20 THEN
      RAISE NOTICE 'value is between eleven and twenty';
  END CASE;
END $$;

Tip

Use simple CASE when you're matching a variable against exact values (like a switch statement). Use searched CASE the moment the condition involves a range, comparison, or anything that isn't equality.

LOOP with an explicit EXIT

sql

DO $$
DECLARE count INTEGER := 10;
BEGIN
  LOOP
    IF count > 100 THEN
      EXIT;
    END IF;
    RAISE NOTICE 'Count %', count;
    count := count + 10;
  END LOOP;
END $$;

A bare LOOP has no built-in exit condition — you provide one yourself with IF ... THEN EXIT, or the more idiomatic EXIT WHEN condition;. Forgetting it is how you write an infinite loop inside a transaction, which is a much worse debugging session than an infinite loop in application code.

Database Normalization: 1NF to 3NF, With the Anomaly Each Step Fixes

Git Commit and Branch Naming Conventions I Actually Follow

Advanced

Backend Notes

The Checklist I Run on Every MyBatis Query Before It Ships

The database will run almost any query — slowly. A five-step self-review that catches index misses and type mismatches before they hit production.

Database Normalization: 1NF to 3NF, With the Anomaly Each Step Fixes

Normalization rules are easy to memorize and hard to apply, because nobody tells you which specific bug each normal form actually prevents. Here's the anomaly behind each step.

PostgreSQLSQLDatabase

8 min read

Intermediate

Backend Notes