PostgreSQL Stored Procedure Example Tutorial

Introduction

Stored Procedures in PostgreSQL play an important role and offer several benefits for database management. In this tutorial, we will explore how to use stored procedures in PostgreSQL to handle SQL operations more efficiently and securely. This article is aimed at software engineers and database administrators seeking to enhance their database management skills through the use of more advanced PostgreSQL features.

What is a Stored Procedure?

Before we start with the examples, it is important to understand the concept of a Stored Procedure. In PostgreSQL, a Stored Procedure is a pre-compiled executable object that contains a series of SQL commands. Its main job is to perform operations on the database such as insert, update, delete, and create. They are particularly useful for operations that involve multiple steps or complex logic, enabling better transaction management and reduced database vulnerability.

Note: The examples and commands in this tutorial are compatible with PostgreSQL 11 and newer versions.

Prerequisites

For this tutorial, you should have the following items installed and configured:

1. PostgreSQL installation on your local machine or server.
2. Command line tool or GUI tool like Beekeeper Studio to run the SQL commands.

Creating a Stored Procedure in PostgreSQL

Historically, PostgreSQL has used the FUNCTION keyword to define routines that can return a value. Functions in PostgreSQL can perform operations and must return a value or a set, which can be a scalar value or a table. They can also be used in SQL queries.

PROCEDURE, introduced in PostgreSQL 11, is used to define routines that do not return a value. Procedures can be used to perform actions such as modifying database state without directly returning data. They are invoked using the CALL statement, which is different from Functions which are often invoked as part of a SELECT statement.

Using PROCEDURE to Update a Table

Let’s start with creating a simple Stored Procedure in PostgreSQL. We will be creating a procedure to update the salary of an employee.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

In the above code, update_employee_salary is the name of the procedure which accepts two parameters - employee ID(p_id) and the new salary(p_salary).

Call a Stored Procedure in PostgreSQL

To call our newly created procedure, we use the CALL command as shown below:

CALL update_employee_salary(1, 50000);

The above command will update the salary of the employee with ID 1 to 50000.

Advanced Usage: Procedures with Conditional Logic

Procedures can also use more complex logic, such as conditional statements and error handling, to perform more sophisticated operations.

This procedure updates an employee’s department based on provided conditions, demonstrating the use of control structures within procedures.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

Expected Output:

CREATE PROCEDURE

Using FUNCTION for Data Retrieval

In contrast to PROCEDURE, a FUNCTION in PostgreSQL is better suited for operations where a return value is expected. This can be particularly useful for data retrieval and computations that need to be part of SQL queries. Here, we will create an example of a function that retrieves information based on specific conditions.

This function will fetch the details of an employee based on their ID. It will demonstrate how functions can be used to return data which can then be directly used in SQL queries.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Expected Output:

CREATE FUNCTION

Invoking the Function

To retrieve data using this FUNCTION, you use the SELECT statement, just like querying a table.

SELECT * FROM get_employee_details(1);

Expected Output:

Here, the output will be the details of the employee with ID 1. If the employee exists, their first name, last name, and department ID will be displayed. If no such employee exists, the result will be empty.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

Conclusion

In this tutorial, we learned how to write Stored Procedures in PostgreSQL using PROCEDURE and FUNCTION and how to invoke them. Understanding how to use Stored Procedures is beneficial for better code organization, security, and execution speed. These examples are simplistic, but the concepts remain the same for more complex operations.

Stored procedures in PostgreSQL offer a robust way to encapsulate logic, ensuring operations are performed efficiently and securely. By integrating stored procedures into your database design, you can achieve more maintainable and reliable database applications. Use this guide as a starting point to explore the extensive possibilities provided by PostgreSQL’s procedural capabilities.