PL/pgSQL Stored Procedures Explained: How to Create and Use Them

Hello, fellow PL/pgSQL enthusiasts! In this blog post, I will introduce you to Procedures in PL/pgSQL – one of the most important and useful concepts in PL/pgSQL: stored procedu

res. Stored procedures are precompiled blocks of code that allow you to perform complex operations directly within the database. They help improve performance, ensure code reusability, and simplify database management. In this post, I will explain what stored procedures are, how to create and execute them, and their key benefits. By the end of this guide, you will have a clear understanding of how to use stored procedures effectively in your PL/pgSQL projects. Let’s get started!

Introduction to PL/pgSQL Stored Procedures: Create and Execute with Ease

Stored procedures in PL/pgSQL are powerful tools that allow you to execute a series of SQL statements as a single unit. They help automate complex tasks, improve code reusability, and enhance database performance. Unlike functions, stored procedures can perform actions without returning a value, making them ideal for handling data modifications and administrative operations. By using stored procedures, you can reduce redundancy, maintain consistency, and improve the efficiency of your database programs. In this guide, you will learn how to create, execute, and optimize stored procedures in PL/pgSQL, empowering you to manage complex database tasks with ease.

What Are Stored Procedures in PL/pgSQL and How to Create and Use Them?

Stored procedures in PL/pgSQL (Procedural Language/PostgreSQL) are precompiled sets of SQL statements that perform a specific task within the database. Unlike functions, stored procedures do not return a value directly but can modify data, execute complex business logic, and interact with the database. They offer better control over transactions and can be used to execute multiple SQL operations in a single call. Stored procedures are especially useful for improving performance, maintaining consistency, and simplifying complex workflows.

Basic Syntax of Stored Procedures in PL/pgSQL

Here’s the basic structure of creating a stored procedure:

CREATE PROCEDURE procedure_name(parameters)
LANGUAGE plpgsql
AS $$
BEGIN
    -- SQL statements
END;
$$;

Example 1: Creating and Calling a Simple Stored Procedure

Let’s create a procedure to add a new employee to an employees table.

Step 1: Create the Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary NUMERIC
);

Step 2: Create the Stored Procedure

CREATE PROCEDURE add_employee(emp_name TEXT, emp_department TEXT, emp_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO employees (name, department, salary)
    VALUES (emp_name, emp_department, emp_salary);
    RAISE NOTICE 'Employee % added successfully!', emp_name;
END;
$$;

Step 3: Execute the Stored Procedure

CALL add_employee('John Doe', 'IT', 75000);

Output:

NOTICE:  Employee John Doe added successfully!

Step 4: Verify the Data

SELECT * FROM employees;

Example 2: Stored Procedure with Transaction Control

Let’s create a procedure that inserts an employee and ensures that data is rolled back on failure.

Step 1: Create the Procedure

CREATE PROCEDURE safe_add_employee(emp_name TEXT, emp_department TEXT, emp_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        INSERT INTO employees (name, department, salary)
        VALUES (emp_name, emp_department, emp_salary);
        COMMIT; -- Save the changes if successful
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK; -- Undo changes on error
            RAISE NOTICE 'Error occurred while adding employee: %', SQLERRM;
    END;
END;
$$;

Step 2: Execute the Procedure

CALL safe_add_employee('Jane Doe', 'HR', 60000);

Example 3: Modifying Existing Data Using Stored Procedure

Suppose you want to update an employee’s salary.

Step 1: Create the Procedure

CREATE PROCEDURE update_salary(emp_id INT, new_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE id = emp_id;
    IF NOT FOUND THEN
        RAISE NOTICE 'Employee with ID % not found!', emp_id;
    ELSE
        RAISE NOTICE 'Salary updated successfully for employee ID %', emp_id;
    END IF;
END;
$$;

Step 2: Call the Procedure

CALL update_salary(1, 85000);

Example 4: Deleting Records Using Stored Procedure

Here’s how to create a procedure to delete an employee by ID.

Step 1: Create the Procedure

CREATE PROCEDURE delete_employee(emp_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM employees WHERE id = emp_id;
    IF NOT FOUND THEN
        RAISE NOTICE 'Employee with ID % not found!', emp_id;
    ELSE
        RAISE NOTICE 'Employee with ID % deleted successfully.', emp_id;
    END IF;
END;
$$;

Step 2: Execute the Procedure

CALL delete_employee(1);

Why do we need Stored Procedures in PL/pgSQL?

Stored procedures in PL/pgSQL are essential for improving database efficiency, managing complex operations, and ensuring consistent data handling. Here are key reasons why they are needed:

1. Improved Performance

Stored procedures execute directly on the database server, which reduces the need for repeated client-server communication. This minimizes network overhead and speeds up the execution of complex operations. Since the logic is processed within the database engine, it enhances efficiency for large datasets and repetitive tasks. This is particularly beneficial for high-traffic applications where performance is critical.

2. Code Reusability

Stored procedures allow you to write a block of code once and reuse it multiple times. This reduces redundancy and improves code maintenance. Instead of duplicating SQL statements across different applications, you can call the procedure whenever needed. It also ensures consistency in business logic and helps in reducing errors caused by repetitive code changes.

3. Transaction Control

Unlike functions, stored procedures in PL/pgSQL can handle transaction management using BEGIN, COMMIT, and ROLLBACK. This means you can control data modifications and ensure they are only finalized when all operations succeed. If any part of the procedure encounters an error, you can roll back changes to maintain data integrity, which is crucial for critical operations like financial transactions.

4. Enhanced Security

Stored procedures can encapsulate complex logic and restrict direct table access. By granting users permission to execute specific procedures instead of directly manipulating tables, you reduce the risk of unauthorized data access or manipulation. This enhances database security by enforcing strict access controls and safeguarding sensitive information.

5. Simplified Maintenance

When business logic is centralized in stored procedures, updating it becomes easier. Instead of modifying SQL queries across multiple applications, you only need to update the procedure. This reduces maintenance effort and ensures consistent logic is applied across all use cases. It also helps prevent errors caused by outdated or inconsistent code.

6. Complex Business Logic

Stored procedures support advanced control structures like loops, conditionals, and error handling. This allows you to implement sophisticated business rules directly in the database. For instance, you can perform dynamic queries, recursive operations, and complex decision-making processes that would be challenging using standard SQL queries alone.

7. Error Handling

PL/pgSQL procedures support robust error handling through the EXCEPTION block. This allows you to catch and manage errors gracefully during execution. You can log errors, send notifications, or roll back transactions in case of failures. This improves system stability by providing better control over unexpected situations and ensures data consistency.

8. Batch Processing

Stored procedures are highly efficient for batch operations, such as inserting or updating large datasets. By processing multiple records in a single execution cycle, you reduce the overhead of executing individual queries. This improves performance and reduces the time needed for data-intensive tasks like data migrations and bulk reporting.

9. Interoperability

You can call stored procedures from various programming languages and external systems, making them ideal for integrating with web applications, APIs, and reporting tools. This cross-platform compatibility allows you to maintain business logic in the database while accessing it from different client environments. It also promotes consistency across diverse application architectures.

10. Consistency and Accuracy

Using stored procedures ensures that business rules are applied consistently across the database. This reduces the likelihood of human errors and discrepancies in data processing. Whether you’re validating inputs, enforcing rules, or processing calculations, stored procedures provide a reliable way to maintain data accuracy and ensure consistent outcomes.

Example of Creating and Using Stored Procedures in PL/pgSQL

Stored procedures in PL/pgSQL allow you to encapsulate SQL logic, making your database operations more efficient, reusable, and easier to maintain. Here are different examples demonstrating how to create and use stored procedures in various scenarios.

1. Basic Stored Procedure (Insert Data)

Let’s start by creating a basic stored procedure to insert records into a table.

Step 1: Create the Table

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC
);

Step 2: Create the Stored Procedure

CREATE OR REPLACE PROCEDURE add_product(p_name TEXT, p_price NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO products (name, price) 
    VALUES (p_name, p_price);
    RAISE NOTICE 'Product % added successfully!', p_name;
END;
$$;

• CREATE OR REPLACE PROCEDURE: Creates a new procedure or updates an existing one.
• Parameters: p_name and p_price accept product name and price.
• RAISE NOTICE: Outputs a success message.

Step 3: Execute the Procedure

CALL add_product('Laptop', 1500);

Output: NOTICE: Product Laptop added successfully!

2. Stored Procedure for Updating Data

Let’s create a procedure to update the price of a product.

Step 1: Create the Procedure

CREATE OR REPLACE PROCEDURE update_product_price(p_id INT, new_price NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE products
    SET price = new_price
    WHERE id = p_id;
    RAISE NOTICE 'Product with ID % updated successfully!', p_id;
END;
$$;

Step 2: Execute the Procedure

CALL update_product_price(1, 1700);

Output: NOTICE: Product with ID 1 updated successfully!

3. Stored Procedure with Conditional Logic

Create a procedure that checks if a product exists before updating the price.

Step 1: Create the Procedure

CREATE OR REPLACE PROCEDURE safe_update_price(p_id INT, new_price NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM products WHERE id = p_id) THEN
        UPDATE products
        SET price = new_price
        WHERE id = p_id;
        RAISE NOTICE 'Product with ID % updated!', p_id;
    ELSE
        RAISE NOTICE 'Product with ID % not found!', p_id;
    END IF;
END;
$$;

Step 2: Execute the Procedure

CALL safe_update_price(2, 1800);

Output: NOTICE: Product with ID 2 updated! (or) NOTICE: Product with ID 2 not found!

4. Stored Procedure for Deleting Data

Let’s create a procedure to delete a product by ID.

Step 1: Create the Procedure

CREATE OR REPLACE PROCEDURE delete_product(p_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM products WHERE id = p_id;
    RAISE NOTICE 'Product with ID % deleted!', p_id;
END;
$$;

Step 2: Execute the Procedure

CALL delete_product(1);

Output: NOTICE: Product with ID 1 deleted!

5. Stored Procedure with Transaction Handling

Ensure data consistency by using transactions. This procedure inserts two products; if any error occurs, the transaction is rolled back.

Step 1: Create the Procedure

CREATE OR REPLACE PROCEDURE add_two_products(p_name1 TEXT, p_price1 NUMERIC, p_name2 TEXT, p_price2 NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        INSERT INTO products (name, price) VALUES (p_name1, p_price1);
        INSERT INTO products (name, price) VALUES (p_name2, p_price2);
        COMMIT;
        RAISE NOTICE 'Both products added successfully!';
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE NOTICE 'Error occurred. Transaction rolled back.';
    END;
END;
$$;

Step 2: Execute the Procedure

CALL add_two_products('Keyboard', 50, 'Mouse', 30);

Output:

• On success: NOTICE: Both products added successfully!
• On failure: NOTICE: Error occurred. Transaction rolled back.

6. Stored Procedure with Loop (Bulk Insertion)

Create a procedure to insert multiple products using a loop.

Step 1: Create the Procedure

CREATE OR REPLACE PROCEDURE bulk_insert_products(p_base_name TEXT, p_price NUMERIC, p_count INT)
LANGUAGE plpgsql
AS $$
DECLARE
    i INT := 1;
BEGIN
    WHILE i <= p_count LOOP
        INSERT INTO products (name, price) 
        VALUES (p_base_name || i, p_price);
        i := i + 1;
    END LOOP;
    RAISE NOTICE 'Inserted % products successfully!', p_count;
END;
$$;

Step 2: Execute the Procedure

CALL bulk_insert_products('Product_', 100, 5);

Output: NOTICE: Inserted 5 products successfully!

7. Stored Procedure with OUT Parameters

Create a procedure to fetch product details by ID and return them using OUT parameters.

Step 1: Create the Procedure

CREATE OR REPLACE PROCEDURE get_product_details(p_id INT, OUT p_name TEXT, OUT p_price NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT name, price INTO p_name, p_price
    FROM products
    WHERE id = p_id;
END;
$$;

Step 2: Execute the Procedure

CALL get_product_details(1, NULL, NULL);

Output: Returns product name and price.

8. Stored Procedure with Dynamic SQL

Use dynamic SQL when table names or queries change at runtime.

Step 1: Create the Procedure

CREATE OR REPLACE PROCEDURE dynamic_delete(p_table TEXT, p_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE format('DELETE FROM %I WHERE id = %s', p_table, p_id);
    RAISE NOTICE 'Record with ID % deleted from %', p_id, p_table;
END;
$$;

Step 2: Execute the Procedure

CALL dynamic_delete('products', 3);

Output: NOTICE: Record with ID 3 deleted from products

Advantages of Creating and Using Stored Procedures in PL/pgSQL

Stored procedures in PL/pgSQL offer several benefits that enhance database performance, security, and maintainability. Here are the key advantages:

1. Improved Performance: Stored procedures execute on the database server, which reduces the need for multiple round trips between the client and the server. This improves the execution speed, especially for complex queries and batch processing, as the logic is processed closer to the data.
2. Code Reusability: Once you create a stored procedure, you can reuse it across different applications and queries. This reduces code duplication, ensures consistent behavior, and simplifies development by maintaining logic in one place.
3. Enhanced Security: Stored procedures provide better access control by limiting direct access to database tables. Users can interact with the data through procedures without needing direct permissions, ensuring sensitive information is protected.
4. Transaction Management: You can manage transactions using BEGIN, COMMIT, and ROLLBACK within stored procedures. This allows you to ensure data integrity by treating multiple operations as a single unit that either completes successfully or is fully undone.
5. Simplified Maintenance: Since the business logic is centralized within stored procedures, any updates or bug fixes can be made in one place without changing client-side code. This reduces maintenance time and the risk of inconsistent logic.
6. Reduced Network Traffic: By executing logic on the database server, stored procedures minimize the amount of data sent between the client and the server. This is especially useful for large datasets or complex operations, reducing the load on network resources.
7. Dynamic Execution: PL/pgSQL supports dynamic SQL within stored procedures, allowing you to build and execute queries based on user inputs or variable conditions. This provides flexibility for handling different scenarios without writing separate procedures.
8. Consistent Business Logic: Storing critical business rules within procedures ensures the same logic is applied consistently across all database interactions. This reduces errors caused by inconsistent implementation across different applications.
9. Error Handling: PL/pgSQL allows for structured error management using EXCEPTION blocks. This enables you to catch, log, and handle errors gracefully, ensuring your processes remain stable even when unexpected issues occur.
10. Modular Design: Breaking down complex workflows into smaller stored procedures improves code organization and readability. This modular approach makes your code easier to maintain, test, and debug, especially in large systems.

Disadvantages of Creating and Using Stored Procedures in PL/pgSQL

Below are the Disadvantages of Creating and Using Stored Procedures in PL/pgSQL:

1. Limited Portability: Stored procedures are database-specific, meaning PL/pgSQL code cannot be easily transferred to other database systems without significant modifications. This limits flexibility when switching between different database platforms.
2. Debugging Complexity: Debugging stored procedures can be challenging due to limited built-in debugging tools. Unlike application-level code, tracing errors and monitoring execution flow requires additional effort using logs or specialized extensions.
3. Performance Overhead: While stored procedures can improve performance, they may also introduce overhead if not optimized properly. Complex logic, large loops, or recursive calls within procedures can slow down execution and consume server resources.
4. Difficult Version Control: Managing changes in stored procedures through version control systems is harder compared to external code files. Tracking modifications and synchronizing updates across different environments requires additional processes.
5. Limited Development Tools: PL/pgSQL has fewer advanced development tools compared to general-purpose programming languages. This can make tasks like refactoring, code completion, and static analysis less convenient.
6. Complex Error Handling: While error handling is supported using EXCEPTION blocks, managing and propagating errors consistently across multiple procedures can become complicated, especially in large systems with nested procedures.
7. Increased Database Load: Heavy use of stored procedures can increase the load on the database server. Shifting complex business logic to the database may lead to performance bottlenecks if not properly managed or distributed.
8. Maintenance Challenges: As the number of stored procedures grows, maintaining and understanding their relationships can become difficult. This is especially true if procedures are interdependent or lack clear documentation.
9. Security Risks: Poorly written stored procedures may expose vulnerabilities like SQL injection if dynamic SQL is not handled safely. Proper validation and security measures are required to mitigate these risks.
10. Longer Deployment Times: Updating or deploying changes to stored procedures often requires careful coordination and database migration scripts. This process can be slower and more complex compared to updating application code.

Future Development and Enhancement of Creating and Using Stored Procedures in PL/pgSQL

Following are the Future Development and Enhancement of Creating and Using Stored Procedures in PL/pgSQL:

1. Improved Debugging Tools: Future versions of PostgreSQL may introduce advanced debugging features, such as step-by-step execution, breakpoints, and real-time variable inspection. These tools will help developers identify errors quickly and make the debugging process easier and more efficient.
2. Enhanced Performance Optimization: Future enhancements may focus on optimizing stored procedure execution by improving caching, reducing execution overhead, and enhancing bulk data handling. This will lead to faster query processing and better performance for complex database operations.
3. Better Integration with External Languages: PostgreSQL may offer more seamless integration with external languages like Python, JavaScript, and R. This will allow developers to combine the power of PL/pgSQL with other languages to handle complex computations and advanced data manipulation tasks.
4. Simplified Version Control: Future developments may provide better support for version control systems, enabling developers to track, compare, and roll back changes to stored procedures. This will improve collaboration in large teams and make managing code across multiple environments easier.
5. Automated Code Analysis and Validation: Upcoming enhancements may introduce automated tools for analyzing stored procedures to detect performance bottlenecks, syntax errors, and security vulnerabilities. This will help developers write cleaner, safer, and more optimized code.
6. Enhanced Security Features: Future versions of PostgreSQL may offer stronger security controls for stored procedures, including advanced privilege management and restricted execution contexts. This will protect sensitive data and prevent unauthorized access to critical database operations.
7. Parallel Execution Support: Stored procedures may gain improved support for parallel execution, allowing them to distribute complex tasks across multiple CPU cores. This will significantly enhance the performance of time-consuming operations, such as batch processing and data aggregation.
8. Graphical User Interface (GUI) Tools: New GUI-based tools may simplify the creation, execution, and monitoring of stored procedures. These user-friendly interfaces will make it easier for developers to visualize logic flows, debug issues, and manage large codebases efficiently.
9. Dynamic Procedure Management: Future enhancements may allow dynamic modification of stored procedures during runtime without requiring recompilation. This will make it easier to adapt to changing business needs and optimize procedure logic on the fly.
10. Cross-Database Compatibility: PostgreSQL may improve interoperability with other database systems, enabling easier migration and data sharing. This enhancement will make it simpler for organizations to integrate PostgreSQL with other relational databases in hybrid environments.