Today we're going to break down how to build a real-world procedure for handling orders. It includes several steps: data validation, updating the order status, and logging. Imagine a restaurant where the chef, waiter, and cashier all need to work together smoothly. In our procedure, we'll implement similar logic for coordinating between steps.
Procedure Task Description
The order processing procedure should do the following steps:
- Check if the needed product is available in the warehouse.
- If there's enough product, subtract its quantity from the warehouse.
- Update the order status so it becomes "Processed".
- Write info about the successful operation to the log.
- If any error happens, roll back all changes to the start.
Procedure Implementation
Step 1. Create the Schema and Tables
Before writing the procedure, let's create the tables it will work with.
Table orders — orders
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
status TEXT DEFAULT 'Pending'
);
This table stores orders. Each order has a customer, product ID, quantity, and status (default is "Pending").
Table inventory — warehouse
CREATE TABLE inventory (
product_id SERIAL PRIMARY KEY,
product_name TEXT NOT NULL UNIQUE,
stock INT NOT NULL CHECK (stock >= 0)
);
This table lists products in the warehouse. Each product has a current stock (stock).
Table order_logs — operation log
CREATE TABLE order_logs (
log_id SERIAL PRIMARY KEY,
order_id INT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
log_message TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
The log will be used to record info about order processing status.
Step 2. Procedure Structure
Here's the structure of the multi-step procedure:
- Check if the requested product is in the warehouse and if there's enough of it.
- If there's enough, decrease its quantity in the
inventorytable. - Change the order status to "Processed".
- Write the successful result to the
order_logstable. - Handle possible errors with rollback.
Step 3. Writing the Procedure
Let's write the process_order procedure to do the steps above.
CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
DECLARE
v_product_id INT;
v_quantity INT;
v_stock INT;
BEGIN
-- Step 1: Get order info
SELECT product_id, quantity
INTO v_product_id, v_quantity
FROM orders
WHERE order_id = $1;
-- Check if the order exists
IF NOT FOUND THEN
RAISE EXCEPTION 'Order with ID % does not exist.', $1;
END IF;
-- Step 2: Check if product is in stock
SELECT stock INTO v_stock
FROM inventory
WHERE product_id = v_product_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Product with ID % does not exist in inventory.', v_product_id;
END IF;
IF v_stock < v_quantity THEN
RAISE EXCEPTION 'Not enough stock for product ID %. Requested: %, Available: %.',
v_product_id, v_quantity, v_stock;
END IF;
-- Step 3: Decrease product quantity in warehouse
UPDATE inventory
SET stock = stock - v_quantity
WHERE product_id = v_product_id;
-- Step 4: Update order status to 'Processed'
UPDATE orders
SET status = 'Processed'
WHERE order_id = $1;
-- Step 5: Write successful operation to log
INSERT INTO order_logs (order_id, log_message)
VALUES ($1, 'Order successfully processed.');
EXCEPTION
WHEN OTHERS THEN
-- Log the error if anything fails
INSERT INTO order_logs (order_id, log_message)
VALUES ($1, 'Error processing order: ' || SQLERRM);
-- Rollback all changes
RAISE;
END;
$$ LANGUAGE plpgsql;Let's break down this procedure.
Validation step:
We check if the given order exists in the
orderstable. If not, we throw an exception with a detailed message. Same thing for checking if the product exists and if there's enough in stock.Warehouse step:
If there's enough product, we decrease its quantity in the warehouse. This is done with an
UPDATE.Order status update step:
We change the status to "Processed" to show the order was completed successfully.
Logging step:
After successful processing, we add a message to the
order_logstable to save info about the operation.Exception handling:
If something goes wrong, we catch the error in the
EXCEPTIONblock, write a detailed error message to the log, and roll back all changes.
Usage Examples
Let's create some test data to check how our procedure works.
-- Add products to warehouse
INSERT INTO inventory (product_name, stock)
VALUES ('Laptop', 10), ('Monitor', 5);
-- Add orders
INSERT INTO orders (customer_name, product_id, quantity)
VALUES
('Alice', 1, 2),
('Bob', 2, 1),
('Charlie', 1, 20); -- This order should cause an errorNow let's test the procedure:
-- Process Alice's order
SELECT process_order(1);
-- Process Bob's order
SELECT process_order(2);
-- Try to process Charlie's order (should error)
SELECT process_order(3);Results:
- Alice's and Bob's orders will be processed successfully, logged, and warehouse stock will decrease.
- Charlie's order will cause an error because there's not enough product in stock, and an error log will appear.
Let's check the tables after running the queries:
SELECT * FROM inventory; -- Stock changes
SELECT * FROM orders; -- Order status changes
SELECT * FROM order_logs; -- Log entries
Common Mistakes and Tips
Mistake: forgot to check
NOT FOUNDafterSELECT INTO.Always handle cases where the query returns nothing, or you'll get unexpected exceptions.
Mistake: didn't add an
EXCEPTIONblock.If you don't have an error handler in your procedure, an exception can hang the transaction or break your logic.
Tip: protect against SQL injection.
Use strictly typed parameters and avoid dynamic SQL unless you really need it.
Extending the Procedure
In real life, you can add more checks, for example:
- Take into account discounts or promos for customers.
- Check the customer's credit limit before processing the order.
- Log not just successful operations, but rollbacks too.
GO TO FULL VERSION