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

Need a 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;

Need a 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;

Need a 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;

Need a hint?

Use RETURN current_stock; to send back the stock value.