Function vs. Stored Procedure in PostgreSQL

1. Introduction

Functions and stored procedures are essential components of SQL. They enable developers to encapsulate reusable code for database operations. While they serve similar purposes, their roles, capabilities, and use cases differ significantly.

Therefore, understanding the differences between functions and stored procedures is crucial for leveraging each construct effectively in database applications.

In this tutorial, we’ll explore the key differences between functions and stored procedures using examples to highlight their characteristics and applications. For demonstration purposes, we’ll use the Baeldung University database.

Notably, we perform all operations in the PostgreSQL database management system (DBMS).

2. Functions in SQL

Functions perform a specific computation or operation and return a single result. Additionally, we use functions for calculations, data transformations, or validations integrated directly into SQL queries.

Let’s review some characteristics of functions:

• Functions always return a value, such as a scalar value, table, or result set
• They produce the same output for the same input which makes them predictable and reliable
• They don’t modify database objects but rather focus solely on data retrieval and computation
• They can be used within SELECT, WHERE, GROUP BY, and HAVING clauses

Let’s show a practical example of the usage of functions in SQL.

2.1. Practical Example

For illustration, we’ll use the Student table of the Baeldung University database. Let’s create a function to calculate the age of students based on their date of birth:

CREATE OR REPLACE FUNCTION CalculateAge(birthDate DATE)
RETURNS INT AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM age(birthDate));
END;
$$ LANGUAGE plpgsql;
Time: 0.015s

Here, we create a function named CalculateAge. Furthermore, the function utilizes PostgreSQL’s built-in age() function to determine the difference between two dates. Since only one argument is provided, age() calculates the difference between the given date and the current date. Consequently, the CalculateAge() function takes birthDate as input and returns the age in years.

Let’s use the CalculateAge function to retrieve the age of students from the Student table:

SELECT id, name, CalculateAge(birth_date) AS age
FROM Student;
+------+-----------------+-----+
| id   | name            | age |
|------+-----------------+-----|
| 1001 | John Liu        | 23  |
| 1003 | Rita Ora        | 23  |
| 1007 | Philip Lose     | 23  |
| 1010 | Samantha Prabhu | 23  |
| 1011 | Vikas Jain      | 23  |
...
SELECT 25
Time: 0.013s

This example demonstrates how functions in SQL simplify repetitive operations, such as age calculations, by encapsulating logic into reusable components. Additionally, it also highlights the seamless integration of functions into SELECT statements for data retrieval and computation.

3. Stored Procedures in SQL

Stored procedures are precompiled SQL code blocks stored in the database. Unlike functions, they perform multiple operations such as modifying data, executing transactions, or returning multiple results. Furthermore, we use stored procedures to implement complex business logic directly within the database.

Here are some characteristics of stored procedures:

• Stored procedures can include multiple SQL statements, which makes them suitable for complex operations
• They can insert, update, delete, or manipulate database objects
• We execute the stored procedure with the use of a CALL statement or equivalent in different database systems
• Stored procedures reduce the overhead of compiling queries each time they’re executed

Let’s show a practical example of the usage of stored procedures in SQL.

3.1. Practical Example

For this demonstration, let’s create a stored procedure that updates the GPA of students in the Student table based on their performance in a specific exam:

CREATE OR REPLACE PROCEDURE UpdateGPA(student_id INT, new_gpa REAL)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE Student
    SET gpa = new_gpa
    WHERE id = student_id;

    RAISE NOTICE 'GPA updated for Student ID: %', student_id;
END;
$$;
Time: 0.003s

The UpdateGPA stored procedure updates a student’s GPA in the Student table based on their ID and the new GPA provided. It then confirms the update with a message.

Before using the stored procedure we created, let’s check the nature of the Student table:

SELECT * FROM student LIMIT 3;
+------+-------------+-------------+------------+-----------------+-----------------+-----+
| id   | name        | national_id | birth_date | enrollment_date | graduation_date | gpa |
|------+-------------+-------------+------------+-----------------+-----------------+-----|
| 1001 | John Liu    | 123345566   | 2001-04-05 | 2020-01-15      | 2024-06-15      | 4.0 |
| 1003 | Rita Ora    | 132345166   | 2001-01-14 | 2020-01-15      | 2024-06-15      | 4.2 |
| 1007 | Philip Lose | 321345566   | 2001-06-15 | 2020-01-15      | 2024-06-15      | 3.8 |
+------+-------------+-------------+------------+-----------------+-----------------+-----+
SELECT 3
Time: 0.006s

Now, let’s invoke the UpdateGPA procedure using the CALL statement:

CALL UpdateGPA(1001, 3.9);
GPA updated for Student ID: 1001
None

CALL
Time: 0.005s

This query updates the GPA of the student whose id is 1001. We can verify the changes by querying the Student table:

SELECT id, name, gpa 
FROM Student
WHERE id = 1001;
+------+----------+-----+
| id   | name     | gpa |
|------+----------+-----|
| 1001 | John Liu | 3.9 |
+------+----------+-----+
SELECT 1
Time: 0.006s

The output shows that the GPA has been updated. This example demonstrates the versatility of stored procedures for handling complex operations.

4. Differences Between Functions and Stored Procedures

Let’s look at the key differences between functions and stored procedures:

The table summarizes the differences between functions and stored procedures.

5. Conclusion

In this article, we’ve explored the differences between functions and stored procedures. We highlighted their unique characteristics and use cases. While functions excel at computations and data retrieval, stored procedures shine in handling complex operations and database modifications.

Finally, by understanding when to use each, we can design efficient and maintainable database applications.