A Comprehensive Guide to SQL - Stored Procedures

Stored procedures in PostgreSQL provide a powerful way to encapsulate complex logic, manage transactions, and handle data efficiently. In this guide, we will explore stored procedures from basic concepts to advanced features and provide practical examples that can enhance your database performance.

What is a Stored Procedure?

A stored procedure is a block of SQL code stored in the database that can be executed repeatedly. Key advantages include:

• Reusability: Frequently used SQL logic can be saved and executed as needed, reducing code duplication.

• Parameterization: Accept parameters for dynamic behavior based on inputs.

• Performance Optimization: Stored procedures are precompiled and cached, reducing execution time.

Syntax for Creating a Stored Procedure

In PostgreSQL, stored procedures are defined using the CREATE PROCEDURE statement. Below is the basic syntax:

CREATE OR REPLACE PROCEDURE procedure_name(parameter_name data_type) LANGUAGE plpgsql AS $$ 
DECLARE -- Variable declaration 
BEGIN -- Procedure body 
- your logic here 
END; 
$$;

Example 1: Calculating Total Sales for a Product

What We Aim to Do:

We aim to calculate the total sales for a specific product by summing up the sales amounts in the sales table for a given product ID.

-- Drop the sales table if exists already
DROP TABLE sales

-- Create Sales Table for Example 
CREATE TABLE sales ( 
sale_id SERIAL PRIMARY KEY, 
product_id INT, 
amount NUMERIC, 
sale_date DATE 
); 


-- Insert sample data into the sales table 
INSERT INTO sales (product_id, amount, sale_date) VALUES      
(1, 100.50, '2024-01-01'), (1, 120.00, '2024-01-04'), 
(1, 130.40, '2024-01-07'), (1, 140.70, '2024-01-10'); 


-- Drop procedure if exists already
DROP PROCEDURE IF EXISTS calculate_total_sales(INT); 



-- Create or replace the procedure to calculate total sales
CREATE OR REPLACE PROCEDURE calculate_total_sales(input_product_id INT) LANGUAGE plpgsql AS $$ 
DECLARE total_sales NUMERIC; 
BEGIN -- Use an alias for product_id 
SELECT SUM(s.amount) INTO total_sales 
FROM sales s 
WHERE s.product_id = input_product_id; -- Raise a notice to display the total sales 
RAISE NOTICE 'Total sales for product ID % is %', input_product_id, total_sales; 
END; $$;



-- Execute the procedure 
CALL calculate_total_sales(1);

This procedure calculates the total sales for the specified input_product_id and raises a notice displaying the result. Here the procedure will calculate the total sales for product ID 1, which sums to $491.60.

Example 2: Calculating Sales in a Date Range

What We Aim to Do:

We want to calculate the total sales of a product within a specific date range to track sales performance over time.

--Drop procedure if exists already
DROP PROCEDURE IF EXISTS calculate_sales_in_range(INT,DATE,DATE);


-- Create or replace the procedure to calculate total sales in data range
CREATE OR REPLACE PROCEDURE calculate_sales_in_range (
input_product_id INT, 
start_date DATE, 
end_date DATE)
LANGUAGE plpgsql 
AS $$
DECLARE
total_sales NUMERIC;
BEGIN
-- Calculate total sales for the product in the given date range    
SELECT SUM(amount) INTO total_sales
FROM sales s
WHERE s.product_id = input_product_id 
AND sale_date BETWEEN start_date AND end_date;
-- Raise a notice to display the total sales
RAISE NOTICE 'Total sales for product ID % from % to % is %', input_product_id, start_date, end_date, total_sales;
END; $$;


-- Example call to calculate sales for product 1 between 2023-01-01 and 2023-12-31
CALL calculate_sales_in_range(1, '2024-01-01', '2024-01-10');

This procedure calculates the total sales for a given product with the product id:1 between 2024-01-01 and 2024-01-10.

Example 3: Transferring Funds Between Accounts

What We Aim to Do:

We want to securely transfers funds between two bank accounts by checking for sufficient balance in the source account, updating both account balances accordingly, and providing transaction status feedback. It ensures that the transfer is atomic, meaning either all operations succeed or none are applied, preventing any account from going negative.

DROP TABLE accounts;

-- Create Accounts Table for Example
CREATE TABLE accounts ( 
    account_id SERIAL PRIMARY KEY, 
    account_name TEXT NOT NULL, 
    balance NUMERIC NOT NULL 
);

-- Insert sample data into the accounts table 
INSERT INTO accounts (account_name, balance) VALUES    
    ('Account A', 1000.00), 
    ('Account B', 500.00);


--Drop procedure if exists already
DROP PROCEDURE IF EXISTS transfer_funds(INT,INT,NUMERIC,TEXT,NUMERIC)


-- Create or replace the procedure to transfer funds
CREATE OR REPLACE PROCEDURE transfer_funds(
from_account INT,
to_account INT,
amount NUMERIC,
OUT transfer_status TEXT,
INOUT from_account_balance NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
-- Get the current balance of the from_account to set the INOUT parameter
SELECT balance INTO from_account_balance
FROM accounts 
WHERE account_id = from_account;
-- Check for insufficient funds
IF from_account_balance < amount THEN
RAISE EXCEPTION 'Insufficient funds in account %', from_account;
END IF;
-- Deduct amount from the source account
UPDATE accounts
SET balance = balance - amount
WHERE account_id = from_account;
-- Add amount to the destination account
UPDATE accounts
SET balance = balance + amount
WHERE account_id = to_account;
-- Update the OUT parameter with the status message
transfer_status := 'Transferred ' || amount || ' from account ' || from_account || ' to account ' || to_account;
-- Update INOUT parameter to reflect the new balance of the from_account
SELECT balance INTO from_account_balance
FROM accounts
WHERE account_id = from_account;
-- Notify of successful transfer
RAISE NOTICE '%', transfer_status;
EXCEPTION
-- Catch any errors and notify
WHEN OTHERS THEN
RAISE NOTICE 'Transaction failed: %', SQLERRM;
END; $$;



-- Call the procedure 
CALL transfer_funds(1, 2, 100); 

Explanation of the Procedure Parameters

1. IN Parameters:

   
   

   • from_account INT: The account ID from which funds will be transferred. This parameter is provided by the caller and is not modified within the procedure.

   • to_account INT: The account ID to which funds will be transferred. This parameter is also provided by the caller and is read-only.

   • amount NUMERIC: The amount of money to transfer from the source account to the destination account. This value is only read within the procedure.

     
     

2. OUT Parameter:

   
   

   • transfer_status TEXT: This parameter returns a status message indicating whether the transfer was successful or not. It is set within the procedure, reflecting the outcome of the fund transfer operation.

     
     

3. INOUT Parameter:

   
   

   • from_account_balance NUMERIC: This parameter represents the balance of the source account. It is initialized with the current balance when the procedure starts and is updated to reflect the new balance after the transfer is completed. The caller can see both the initial balance and the updated balance after the procedure execution.

The procedure retrieves the current balance of the from_account, checks for sufficient funds, performs the transfer, updates the transfer_status, and sets the final balance back to the caller through the from_account_balance parameter.

Differences Between Stored Procedures and Functions in PostgreSQL

Feature	Stored Procedure	Function
Return Type	Does not return a value.	Must return a value (e.g., scalar, table).
Execution	Called using CALL statement.	Called like any expression within a query.
Transaction Control	Supports explicit transaction control (BEGIN, COMMIT).	No control over transactions within the function.
Usage in Queries	Cannot be used in SELECT or other SQL queries.	Can be used in SELECT, INSERT, UPDATE, DELETE.
Parameter Modes	Supports IN, OUT, and INOUT parameters.	Only supports IN parameters.
Use Case	Best for operations that don't need to return data (e.g., updates, inserts).	Best for returning a result set or single value.

When to Use a Procedure vs. a Function?

• Use a Stored Procedure when you need to perform operations such as data updates, inserts, or transactions without expecting a return value.

• Use a Function when you need to return data that can be used in a SQL query, such as calculations or data transformations.

Stored procedures in PostgreSQL are invaluable for managing complex business logic and optimizing database performance. By encapsulating logic into reusable procedures, you reduce code duplication, improve maintainability, and enhance efficiency. Choosing between procedures and functions depends on the nature of the operation—procedures are ideal for updates and transactions, while functions are better suited for returning data that can be used in queries.