Create stored procedures and functions with PostgreSQL

Image by freepik

Creating stored procedures in PostgreSQL is a powerful way to encapsulate business logic within the database. The developer can apply stored procedures in real-life projects such as generating monthly report data, cleaning up logs tables and Data Migration.

Pros of Stored Procedures

Performance Optimization: Stored procedures are executed on the database server, which reduces database connection.

Security: reducing the risk of SQL injection attacks.

Maintainability: business logic can be made in one place
(the stored procedure).

Cons of Stored Procedures

Version control: Managing stored procedures separately from application code can add complexity and require additional effort in coordinating changes.

Scaling: Stored procedures execute on the database server, which can lead to high resource consumption. Scaling the database server is more complex than scaling up an application.

Performance Bottlenecks: stored procedures on the database server when multiple application services called stored procedures may impact performance.

Read more about creating a PostgreSQL database server and pgAdmin.

Example: Creating a Simple Stored Procedure

This example will use the tool pgAdmin to create stored procedures.

Basic Structure

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

Read more about pgAdmin and PostgreSQL.

Create stored procedures query data from table user_profiles and return cursor.

Create stored procedures name get_user_profiles

Implement SQL following business logic.

The developer can save SQL statements to create stored procedures.

Click the “save” button. The stored procedure has been created.

Testing call stored procedure

CALL get_user_profiles(null);
FETCH ALL IN "<unnamed portal 1>";

Press the “F5” button to execute SQL.

“<unnamed portal 1>” starts from 1 every time the developer executes a command. Unnamed portal will run numbers 1, 2, 3, …

Example: Creating a Simple Functions

Basic structure

CREATE OR REPLACE FUNCTION function_name(parameter_name1 parameter_type1, parameter_name2 parameter_type2)
RETURNS return_type
LANGUAGE plpgsql
AS $$
BEGIN
    -- Function logic goes here
    RETURN some_value;
END;
$$;

Create a function to calculate age input “birthdate” and “date”. and return age.

Create function name calculate_age and implement SQL following business logic.

CREATE OR REPLACE FUNCTION calculate_age(birthdate DATE, date_to DATE)
RETURNS int
LANGUAGE plpgsql
AS
$$
DECLARE
    age integer;
BEGIN
    -- Calculate the age using the age function and extract the year part
    age := DATE_PART('year', AGE(date_to, birthdate));
    RETURN age;
END;
$$;

Testing call functions.

Place statement in query tool for testing calculate_age function.

SELECT public.calculate_age('1990-01-01', '2023-05-20')

Test results show age between birthdate and date to.

Implement calculate age function with table.

Test function by query user_profiles table and calculate age using column birthdate and current date.

select up.*,
public.calculate_age(birth_date, CURRENT_DATE) as age  
from public.user_profiles up;

Test results show the age between birthdate and the current date with a result set from user_profiles table data.

Organize stored procedures and functions with schema

In the previous section, stored procedures and functions inside the public schema. The developer can organize stored procedures and functions in separate schemas to follow tasks or business logic.

In the database, the demo contains a public schema.

The developer can create a “user_management” schema for working with the user on tasks such as activity or monthly reports.

Create “user_management” schema

CREATE SCHEMA user_management AUTHORIZATION admin;

The developer can move the “user_profiles” table to the “user_management” schema for organizing the database.

Example using schema

In SQL statements, when executing the stored procedure, functions, or table, a schema must be defined to determine which schema to access.

Query

select * from user_management.user_profiles;

Function

select up.*,
       user_management.calculate_age(birth_date, CURRENT_DATE) as age  
from user_management.user_profiles up;

Stored procedure

SELECT user_management.calculate_age('1990-01-01', '2023-05-20')

The example shows a change from the “public” schema to the “user_management” schema.

Schema helps the developer organize a database for maintainability and access control. The developer can grant database users access to specific schemas.

Finally, PostgreSQL is a powerful database that provides various features.
stored procedures and functions help the developer manipulate data in the database. The developer should learn about stored procedures and functions to help the developer enhance database management skills to implement batch processing or data analysis.

Thank you for reading.