IF and ELSIF in SQL

A stock verdict picked by walking conditions in order until one is true.

-- IF walks its conditions in order; the first true branch wins.
DO $$
DECLARE
    vest_stock INTEGER;
    verdict TEXT;
BEGIN
    SELECT stock
    INTO vest_stock
    FROM products
    WHERE sku = 'PFD-M';
    IF vest_stock = 0 THEN
        verdict := 'reorder now';
    ELSIF vest_stock < 5 THEN
        verdict := 'running low';
    ELSE
        verdict := 'plenty on the shelf';
    END IF;
    RAISE NOTICE 'vests: % (%)', verdict, vest_stock;
END;
$$;

How it works

1. IF vest_stock = 0 THEN handles the empty shelf first.
2. ELSIF chains further tests without nesting; ELSE catches the rest.
3. The whole construct closes with END IF, period included.

Keywords and builtins used here

• BEGIN
• DECLARE
• DO
• ELSE
• ELSIF
• END
• FROM
• IF
• INTEGER
• INTO
• SELECT
• TEXT
• THEN
• WHERE

The run, in numbers

Lines

20

Characters to type

389

Tokens

65

Three-star pace

70 tpm

At the three-star pace of 70 tokens a minute, this run takes about 56 seconds.

Type this snippet

Step 3 of 4 in PL/pgSQL blocks, step 7 of 23 in PostgreSQL Stored Procedures.

← Previous Next →