Mastering Stored Procedures in PostgreSQL A Practical Guide

PostgreSQL, a powerful open-source relational database, offers many features that simplify backend development and data handling. One such feature is Stored Procedures—a game-changer for automating database operations, improving performance, and encapsulating business logic directly within the database.

📌 What Are Stored Procedures?

A Stored Procedure is a precompiled collection of one or more SQL statements stored in the database and executed as a single unit. Unlike functions in PostgreSQL, procedures can:

• Perform transactions (COMMIT/ROLLBACK)

• Return void or sets of values

• Include control-flow logic like loops and conditionals

✅ Benefits of Using Stored Procedures

• Performance: Precompiled and cached for repeated execution.

• Security: You can grant permission on a procedure without exposing underlying tables.

• Maintainability: Business logic is centralized in the database.

• Atomicity: Great for complex operations that need full rollback on error.

Before creating a stored procedure, Let's first create a table and insert values so that we can write a stored procedure on it:

1) Create Table employees

CREATE TABLE employees (
	 id SERIAL PRIMARY KEY,
	 name TEXT NOT NULL,
	 email TEXT UNIQUE NOT NULL,
     salary NUMERIC(10, 2) NOT NULL,
     hire_date DATE DEFAULT CURRENT_DATE
);

Explanation:

• id: Auto-incremented primary key.

• name: Employee name.

• email: Must be unique.

• salary: Salary with two decimal places.

• hire_date: Defaults to the current date if not provided.

  
  

2) Insert sample data into table employees

INSERT INTO employees (name, email, salary, hire_date) 
VALUES
('Alice Johnson','alice.johnson@example.com',60000.00,'2022-06-15'),
('Bob Smith','bob.smith@example.com',85000.00,'2023-01-10'),('Charlie Lee','charlie.lee@example.com',70000.00,'2021-11-20'),('Diana Prince','diana.prince@example.com',65000.00,'2023-03-05'),
('Ethan Ray', 'ethan.ray@example.com', 92000.00, '2022-09-01');

3) Query the table

SELECT * FROM employees;

Sample O/P:

🛠️ Creating a Stored Procedure

Let’s create a simple stored procedure that inserts a new employee record.

CREATE PROCEDURE insert_employee(
 emp_name TEXT,
 emp_email TEXT,
emp_salary NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
 INSERT INTO employees(name, email, salary)
 VALUES (emp_name, emp_email, emp_salary);
END;
$$;

🟡 To execute the above procedure:

CALL insert_employee('John Doe', 'john@example.com', 75000);

Transactional Stored Procedures

A transactional stored procedure in PostgreSQL is a procedure created using CREATE PROCEDURE , which can explicitly manage transactions using:

• BEGIN

• COMMIT

• ROLLBACK

These stored procedures are ideal for grouping multiple DML operations (INSERT, UPDATE, DELETE) and ensuring atomicity—meaning all actions succeed or none are applied. In PostgreSQL, stored procedures  support transaction control—something that regular functions (CREATE FUNCTION) do not support.

🔄 Use Case: Adjust Employee Salary with a Limit Check

We’ll create a stored procedure that:

• Increases an employee’s salary

• Aborts the update if the new salary exceeds a defined cap

• Uses a transaction to ensure either everything happens or nothing does

CREATE PROCEDURE adjust_employee_salary(
emp_email TEXT,
increment NUMERIC,
max_allowed NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
  current_salary NUMERIC;
  new_salary NUMERIC;
BEGIN
	-- Start transaction block implicitly
 	SELECT salary INTO current_salary
 	FROM employees
 	WHERE email = emp_email;

 	IF NOT FOUND THEN
 	RAISE EXCEPTION 'Employee with email % not found.', emp_email;
 	END IF;

 	new_salary := current_salary + increment;
 	
	IF new_salary > max_allowed THEN
	    RAISE EXCEPTION 'New salary % exceeds the allowed limit of 	%',new_salary, max_allowed;
 	END IF;

 	UPDATE employees
 	SET salary = new_salary
 	WHERE email = emp_email;

 	-- Transaction commits automatically if no errors
END;
$$;

💡 How the above code Works

1. Fetches current salary.

2. Adds the increment.

3. If the new salary exceeds the limit, it throws an exception → rolls back the operation.

4. If valid, it updates the salary.

➕ Sample Calls

-- This will succeed
CALL adjust_employee_salary('alice.johnson@example.com', 3000, 70000);
-- This will raise an error and rollback
CALL adjust_employee_salary('bob.smith@example.com', 10000, 90000);

📈 Real-World Use Cases

• Batch processing (e.g., monthly billing)

• Audit trails and data versioning

• Complex validation before inserts/updates

• Automated data archival and cleanup

📋 Conclusion

Stored procedures in PostgreSQL offer a powerful way to streamline operations, ensure data integrity, and enforce consistent logic directly within your database layer. Whether you're building a banking system or a data pipeline, understanding stored procedures can help you write cleaner, faster, and more secure applications.