How to Implement PostgreSQL Stored Procedures

Introduction

In PostgreSQL, a stored procedure is a user-defined function that runs multiple queries to automate business processes. For example, in a bank application, you can define a stored procedure to deactivate user accounts, transfer money between accounts, or automate the loan account creation process.

A stored procedure allows you to move some business logic to the database level. This architecture promotes code reuse by allowing different mobile, desktop, and web-based frontend applications to share the same backend logic.

This guide explains how to implement a stored procedure that takes user variables to create loan accounts for clients. The stored procedure inserts data in a predefined database table and computes the interest amount for a client based on the principal amount, annual interest rate, and repayment period.

Prerequisites

Before you begin, be sure to:

• Deploy a Linux server on Vultr.
• Create a non-root sudo user.
• Install PostgreSQL.

The Stored Procedure Syntax

To create a stored procedure in a PostgreSQL server follow the syntax below:

CREATE PROCEDURE SAMPLE_PROCEDURE_NAME (SAMPLE_ARGUMENT_1, SAMPLE_ARGUMENT_2, ...)  
    SAMPLE_LANGUAGE_NAME
    SAMPLE_SQL_BODY

• The SAMPLE_PROCEDURE_NAME is a unique name that identifies the name of the procedure you want to create in the database.
• After defining the name of your procedure, you should enclose the expected arguments in brackets (SAMPLE_ARGUMENT_1, SAMPLE_ARGUMENT_2, ...).
• The SAMPLE_LANGUAGE_NAME declares the stored procedure query language such as plpgsql or sql. This guide implements the sql language.
• The SAMPLE_SQL_BODY is the code block that the procedure executes to fulfill a business logic in the database.

Set up the PostgreSQL Database

In this section, log in to the PostgreSQL server, set up a database, and create tables. The example application requires the following tables:

• clients table stores clients' names and their unique client_ids.
• loan_accounts table stores the clients' loan information such as principal amount, Annual Percentage Rate (APR), interest amount, and repayment period in months.

The example application implements a stored procedure that automates inserting data directly to the loan_accounts table.Application users should only run the stored procedure and supply the values of the arguments to create the loan account.

Follow the steps below to set up the database and create the required tables:

1. Log in to the PostgreSQL server as user postgres.

    $ sudo -u postgres psql

2. Create a sample microfinance database.

    postgres=# CREATE DATABASE microfinance;

3. Switch to the new microfinance database.

    postgres=# \connect microfinance;

4. Create a clients table.

    microfinance=# CREATE TABLE clients (
                        client_id SERIAL PRIMARY KEY,
                        first_name VARCHAR(50),
                        last_name VARCHAR(50) 
                   );

5. Populate the clients table with sample data.

    microfinance=# INSERT INTO clients (first_name, last_name) VALUES ('JOHN', 'DOE');
                   INSERT INTO clients (first_name, last_name) VALUES ('MARY', 'SMITH');
                   INSERT INTO clients (first_name, last_name) VALUES ('ESTHER', 'AIDA');

6. Create a loans table.

    microfinance=# CREATE TABLE loan_accounts (
                       loan_id SERIAL PRIMARY KEY,
                       client_id INTEGER, 
                       principal_amount DECIMAL(17,4),
                       apr NUMERIC,
                       interest_amount DECIMAL(17,4),
                       repayment_period INTEGER
                    );

Create and Run a PostgreSQL Stored Procedure

To create a loan account, the example application uses values with the following arguments:

• client_id INTEGER.
• principal_amount DECIMAL(17,4).
• apr NUMERIC.
• repayment_period INTEGER.

1. To allow PostgreSQL to validate input data, define a datatype for each argument by running the following command to create a stored procedure.

    microfinance=#  CREATE PROCEDURE create_loan_account(client_id INTEGER, principal_amount DECIMAL(17,4), apr NUMERIC, repayment_period INTEGER)
                      LANGUAGE SQL
                      AS $$
   
                      INSERT INTO loan_accounts (
                           client_id, 
                           principal_amount, 
                           apr, 
                           interest_amount, 
                           repayment_period
                       ) VALUES (
                           client_id, 
                           principal_amount, 
                           apr, 
                           (principal_amount * apr / 100 * (repayment_period/12)), 
                           repayment_period
                       );
   
                      $$;

   Use the $$ characters as a delimiter to define where the stored procedure SQL body starts and ends. The formula (principal_amount * apr / 100 * (repayment_period/12)) computes the total interest amount a client should pay for the loan.

   You've created the stored procedure named create_loan_account. Follow the steps below to test the stored procedure:

2. Use the CALL function to create a loan with a principal amount of $90,000 and an interest amount of 18.5% repayable in 36 months.

    microfinance=# CALL create_loan_account(1, 90000, 18.5, 36);

3. Create two more loans using the same syntax.

    microfinance=# CALL create_loan_account(2, 50000, 12.6, 24);
                  CALL create_loan_account(3, 60000, 19.65, 12);

4. Query the loan_accounts table to verify if the stored procedure works as expected.

    microfinance=# SELECT
                       loan_id,
                       client_id, 
                       principal_amount,
                       apr,
                       interest_amount,
                       repayment_period
                   FROM loan_accounts;

   Your output should look like the one below. Verify that the stored procedure computes the interest amount as expected and successfully populates the loan_accounts table.

     loan_id | client_id | principal_amount |  apr  | interest_amount | repayment_period
    ---------+-----------+------------------+-------+-----------------+------------------
           1 |         1 |       90000.0000 |  18.5 |      49950.0000 |               36
           2 |         2 |       50000.0000 |  12.6 |      12600.0000 |               24
           3 |         3 |       60000.0000 | 19.65 |      11790.0000 |               12
    (3 rows)

Next Steps

In this guide, you have implemented a PostgreSQL stored procedure to automate a logic for creating clients' loan accounts. To implement more PostgreSQL functions, visit the following resources.

• How to Implement Arrays with Vultr Managed Databases for PostgreSQL.
• How to Use Enumerated Data Types with PostgreSQL.
• How to Implement PostgreSQL Database Transactions with Python on Ubuntu 20.04.