RAISE for notices and exceptions in PostgreSQL - Mini Project: Build & Apply

Using RAISE for Notices and Exceptions in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a small online store. You want to create a simple stored procedure that checks the stock of a product before processing an order. If the stock is low, you want to notify the user with a notice. If the stock is zero or less, you want to stop the process with an exception.

🎯 Goal: Create a PostgreSQL function that uses RAISE NOTICE to warn when stock is low and RAISE EXCEPTION to stop when stock is unavailable.

📋 What You'll Learn

Create a table called products with columns product_id (integer) and stock (integer).

Insert a product with product_id 1 and stock 5.

Create a function called check_stock that takes p_product_id as an integer parameter.

Inside the function, get the stock for the given product.

If stock is less than 3 but greater than 0, use RAISE NOTICE to warn 'Stock is low'.

If stock is 0 or less, use RAISE EXCEPTION to stop with message 'Out of stock'.

If stock is sufficient, return the stock value.

💡 Why This Matters

🌍 Real World

Using RAISE statements helps database developers communicate important information or errors during stored procedure execution, improving debugging and user feedback.

💼 Career

Database developers and administrators often write stored procedures that need to handle errors and warnings gracefully. Knowing how to use RAISE is essential for robust database programming.

1

Create the products table and insert data

Create a table called products with columns product_id as integer and stock as integer. Then insert one row with product_id 1 and stock 5.

PostgreSQL

-- Create the products table and insert one product
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the product.

2

Create the check_stock function header and variable

Create a function called check_stock that takes one integer parameter p_product_id. Declare a variable current_stock of type integer to hold the stock value.

PostgreSQL

CREATE OR REPLACE FUNCTION check_stock(p_product_id INTEGER) RETURNS INTEGER AS $$
DECLARE
    current_stock INTEGER;
BEGIN
    -- Your code here
END;
$$ LANGUAGE plpgsql;

Hint

Use CREATE OR REPLACE FUNCTION with DECLARE to define variables.

3

Retrieve stock and add RAISE NOTICE and EXCEPTION

Inside the check_stock function, select the stock from products where product_id equals p_product_id into current_stock. Then add an IF statement: if current_stock is less than 3 and greater than 0, use RAISE NOTICE with message 'Stock is low'. If current_stock is 0 or less, use RAISE EXCEPTION with message 'Out of stock'.

PostgreSQL

CREATE OR REPLACE FUNCTION check_stock(p_product_id INTEGER) RETURNS INTEGER AS $$
DECLARE
    current_stock INTEGER;
BEGIN
    SELECT stock INTO current_stock FROM products WHERE product_id = p_product_id;
    -- Add IF statements with RAISE NOTICE and RAISE EXCEPTION here
END;
$$ LANGUAGE plpgsql;

Hint

Use SELECT ... INTO to get the stock. Use IF ... ELSIF ... END IF; for conditions.

4

Return the stock value to complete the function

At the end of the check_stock function, add a RETURN statement to return the current_stock value.

PostgreSQL

CREATE OR REPLACE FUNCTION check_stock(p_product_id INTEGER) RETURNS INTEGER AS $$
DECLARE
    current_stock INTEGER;
BEGIN
    SELECT stock INTO current_stock FROM products WHERE product_id = p_product_id;
    IF current_stock  0 THEN
        RAISE NOTICE 'Stock is low';
    ELSIF current_stock <= 0 THEN
        RAISE EXCEPTION 'Out of stock';
    END IF;
    -- Add RETURN statement here
END;
$$ LANGUAGE plpgsql;

Hint

Use RETURN current_stock; to send back the stock value.

Practice

1. What does the RAISE NOTICE statement do in PostgreSQL?

easy

A. It silently logs a message without showing it to the user.

B. It stops the code execution and returns an error.

C. It sends a message to the client but continues running the code.

D. It restarts the current transaction automatically.

Solution

1. Step 1: Understand RAISE NOTICE behavior

   RAISE NOTICE sends an informational message to the client but does not stop the execution.

2. Step 2: Compare with other RAISE levels

   Unlike EXCEPTION, which stops execution, NOTICE only shows a message and continues.

3. Final Answer:

   It sends a message to the client but continues running the code. -> Option C

4. Quick Check:

   RAISE NOTICE = message without stopping [OK]

Hint: NOTICE shows messages but never stops code [OK]

Common Mistakes:

• Confusing NOTICE with EXCEPTION stopping code
• Thinking NOTICE hides messages
• Assuming NOTICE restarts transactions

2. Which of the following is the correct syntax to raise a warning message in a PostgreSQL function?

easy

A. RAISE WARNING 'This is a warning';

B. RAISE WARN 'This is a warning';

C. RAISE WARNNING 'This is a warning';

D. RAISE WARN_MSG 'This is a warning';

Solution

1. Step 1: Recall correct RAISE syntax for warnings

   The correct keyword is WARNING, not WARN or other variants.

2. Step 2: Validate syntax correctness

   RAISE WARNING 'message'; is the valid syntax to raise a warning message.

3. Final Answer:

   RAISE WARNING 'This is a warning'; -> Option A

4. Quick Check:

   RAISE WARNING = correct syntax [OK]

Hint: Use full word WARNING, not abbreviations [OK]

Common Mistakes:

• Using 'WARN' instead of 'WARNING'
• Misspelling WARNING as WARNNING
• Inventing unsupported keywords like WARN_MSG

3. Consider this PostgreSQL PL/pgSQL block:

DO $$
BEGIN
  RAISE NOTICE 'Step 1';
  RAISE WARNING 'Step 2';
  RAISE EXCEPTION 'Step 3';
  RAISE NOTICE 'Step 4';
END $$;

What will be the output?

medium

A. Step 1 and Step 4 notices print, but warning and exception are ignored.

B. All four messages print: Step 1, Step 2, Step 3, Step 4.

C. Only Step 3 error message is shown, others are ignored.

D. Step 1 (notice), Step 2 (warning), then error 'Step 3', and stops before Step 4.

Solution

1. Step 1: Trace the RAISE statements in order

   RAISE NOTICE 'Step 1' prints a message and continues.

2. Step 2: Understand RAISE WARNING and EXCEPTION effects

   RAISE WARNING 'Step 2' prints a warning and continues. RAISE EXCEPTION 'Step 3' raises an error and stops execution immediately.

3. Step 3: Check if Step 4 runs

   Since EXCEPTION stops execution, RAISE NOTICE 'Step 4' does not run.

4. Final Answer:

   Step 1 (notice), Step 2 (warning), then error 'Step 3', and stops before Step 4. -> Option D

5. Quick Check:

   EXCEPTION stops code after Step 3 [OK]

Hint: EXCEPTION stops code; notices/warnings do not [OK]

Common Mistakes:

• Assuming all messages print despite EXCEPTION
• Thinking WARNING stops execution
• Ignoring that EXCEPTION halts code immediately

4. You wrote this code inside a PostgreSQL function:

RAISE EXCEPTION 'Error: %', message;

But it causes an error: "ERROR: column "message" does not exist". What is the likely problem?

medium

A. The variable 'message' is not declared or out of scope.

B. RAISE EXCEPTION cannot use placeholders like '%'.

C. You must use RAISE WARNING instead of EXCEPTION for variables.

D. The syntax requires double quotes around 'message'.

Solution

1. Step 1: Understand the error message

   The error says column "message" does not exist, meaning 'message' is treated as a column or variable but is undefined.

2. Step 2: Check variable declaration and scope

   In PL/pgSQL, variables must be declared before use. If 'message' is not declared or out of scope, this error occurs.

3. Final Answer:

   The variable 'message' is not declared or out of scope. -> Option A

4. Quick Check:

   Undefined variable causes this error [OK]

Hint: Declare variables before using in RAISE [OK]

Common Mistakes:

• Thinking placeholders '%' are invalid
• Using RAISE WARNING instead of EXCEPTION for variables
• Adding quotes around variable names incorrectly

5. You want to write a PostgreSQL function that checks if a user age is below 18 and raises an exception with a custom message including the age. Which code snippet correctly does this?

hard

A. IF age < 18 THEN RAISE EXCEPTION 'User age % is too young' || age; END IF;

B. IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', age; END IF;

C. IF age < 18 THEN RAISE EXCEPTION MESSAGE = 'User age ' || age || ' is too young'; END IF;

D. IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', 'age'; END IF;

Solution

1. Step 1: Understand RAISE EXCEPTION with placeholders

   RAISE EXCEPTION supports printf-style placeholders like % and variables as arguments.

2. Step 2: Check each option's correctness

   IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', age; END IF; correctly uses the % placeholder followed by the variable age as an argument.
   IF age < 18 THEN RAISE EXCEPTION 'User age % is too young' || age; END IF; incorrectly concatenates the variable to the format string, resulting in an unreplaced % and a runtime format error.
   IF age < 18 THEN RAISE EXCEPTION MESSAGE = 'User age ' || age || ' is too young'; END IF; omits the required USING keyword before MESSAGE=, causing a syntax error.
   IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', 'age'; END IF; passes the string literal 'age' instead of the variable.

3. Final Answer:

   IF age < 18 THEN RAISE EXCEPTION 'User age % is too young', age; END IF; -> Option B

4. Quick Check:

   Use % and variable for dynamic messages [OK]

Hint: Use % placeholder with variable, not string 'age' [OK]

Common Mistakes:

• Concatenating strings instead of using placeholders
• Passing variable name as string instead of variable
• Using incorrect RAISE EXCEPTION syntax