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 log tables, and Data Migration.

Pros of Stored Procedures

Performance Optimization: Stored procedures are executed on the database server, which reduces the number of database connections.

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 are stored procedures on the database server, when multiple application services are called, stored procedures, which may impact performance.

How to Set Up PostgreSQL Database Server with Docker

Learn how to set up a PostgreSQL database server using Docker. Create, manage, and test databases effortlessly in a controlled environment.

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;
$$;

pgAdmin – PostgreSQL Tools

pgAdmin – PostgreSQL Tools for Windows, Mac, Linux and the Web

PostgreSQL

The world’s most advanced open source database.

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

Create stored procedures named 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 Function

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 a function named 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 the statement in the query tool to test the calculate_age function.

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

Test results show the age between the birthdate and the date.

Implement a calculate age function with the table.

Test function by querying the user_profiles table and calculating age using the column birthdate and the current date.

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

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

Organize stored procedures and functions with schema

The previous section stores 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 users on tasks such as activity or monthly reports.

Create “user_management” schema

CREATE SCHEMA user_management AUTHORIZATION admin;

To organize the database, the developer can move the “user_profiles” table to the “user_management” schema.

Example using schema

When executing the stored procedure, functions, or table in SQL statements, 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.