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:
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 asplpgsql
orsql
. This guide implements thesql
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 uniqueclient_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:
Log in to the PostgreSQL server as user
postgres
.$ sudo -u postgres psql
Create a sample
microfinance
database.postgres=# CREATE DATABASE microfinance;
Switch to the new
microfinance
database.postgres=# \connect microfinance;
Create a
clients
table.microfinance=# CREATE TABLE clients ( client_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );
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');
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.
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:Use the
CALL
function to create a loan with a principal amount of$90,000
and an interest amount of18.5%
repayable in36
months.microfinance=# CALL create_loan_account(1, 90000, 18.5, 36);
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);
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.