PostgreSQL Introduction to Stored Procedures (original) (raw)

Last Updated : 15 Jul, 2025

**PostgreSQL allows the users to extend the **database functionality with the help of **user-defined functions and **stored procedures through various **procedural language elements, which are often referred to as **stored procedures.The store procedures define **functions for **creating triggers or custom aggregate functions.

In this article, we will explain how **PostgreSQL stored procedures work, their **benefits, and **disadvantages, and also go through some examples of creating and using **stored procedures. Let's understand the world of **PostgreSQL stored procedures in depth to enhance our **database functionality.

PostgreSQL Stored Procedures

Stored procedures provide a means to execute multiple SQL statements in a **structured and **reusable way. They introduce advanced control structures and allow us to perform **complex calculations, making it easier to build complex applications directly within the database. By default, **PostgreSQL supports three main **procedural languages: SQL, PL/pgSQL, and C. We can also add other languages, such as **Perl, Python, and TCL, through extensions.

PostgreSQL divides **procedural languages into two main categories:

1. **Safe Languages: These can be used by any user and include languages like **SQL and **PL/pgSQL.
2. **Sandboxed Languages: These are restricted to superusers because they can bypass security measures and access **external resources. An example of a sandboxed language is C.

Advantages of using PostgreSQL stored procedures

• **Reduced network traffic: Stored procedures execute on the database server, so the application sends a single function call instead of multiple SQL statements, reducing the number of database round trips.
• **Increased performance: Increase application performance because the **user-defined functions and **stored procedures are pre-compiled and stored in the PostgreSQL database server.
• **Reusability: You can reuse stored procedures across multiple applications, making database management more efficient.
• **Centralized logic: With **stored procedures, logic is centralized on the database server, which reduces the redundancy of code in different applications.

Disadvantages of using PostgreSQL stored procedures:

• **Specialized skills required: Slowness in software development because stored procedure programming requires specialized skills that many developers do not possess.
• **Version management: Difficult to manage versions and hard to debug.
• **Portability issues: May not be portable to other database management systems e.g., MySQL or Microsoft SQL Server.

Example: Creating a Simple PostgreSQL Stored Procedure

To understand how **stored procedures work in PostgreSQL, let's walk through an example. We'll create a **simple stored procedure that transfers funds from one account to another in a **banking system.

**Step 1: Create an Accounts Table

First, create a table called **accounts**with columns for id, name, and balance. This table will serve as the foundation for our stored procedure.

**Query:

drop table if exists accounts;

create table accounts (
id int generated by default as identity,
name varchar(100) not null,
balance dec(15, 2) not null,
primary key(id)
);

insert into accounts(name, balance)
values('Raju', 10000);
insert into accounts(name, balance)
values('Nikhil', 10000);

The following query will show the table data:

select * from accounts;

**Output

Stored Procedure result

Step 2: Create a Stored Procedure to Transfer Funds

The following query creates a stored procedure named transfer that transfers a specified amount of money from one account to another.

create or replace procedure transfer(
sender int,
receiver int,
amount dec
)
language plpgsql
as
begin
-- subtracting the amount from the sender's account
update accounts
set balance = balance - amount
where id = sender;

-- adding the amount to the receiver's account  
update accounts   
set balance = balance + amount   
where id = receiver;

commit;  

end;$$;

Step 3: Call the Stored Procedure

To call a stored procedure, you use the CALL statement as follows:

call stored_procedure_name(argument_list);

**Example:

The below statement invokes the transfer stored procedure to transfer ****$1, 000** from **Raju's account to **Nikhil's account:

call transfer(1, 2, 1000);

Step 4: Verify the Data

The following statement verifies the data in the accounts table after the transfer:

SELECT * FROM accounts;

**Output

Stored Procedure result1

**Stored Procedures vs. Functions in PostgreSQL

Stored procedures are often compared to **functions in PostgreSQL. Here’s a quick comparison:

• **Stored procedures: Do not return a value and are mainly used to perform operations like **data modification, transaction management, etc.
• **Functions: Return a value and are mainly used to fetch data or perform calculations.

For example, a function in **PostgreSQL would return a value, while a **procedure, as demonstrated above, does not return a value but rather executes a task.

Conclusion

**PostgreSQL stored procedures provide a **powerful tool for database administrators and **developers, enabling them to perform complex operations, reuse code, and improve the efficiency of applications. By centralizing the logic on the database server, **stored procedures help reduce network traffic and boost application performance. Whether we are performing **fund transfers, **calculating payroll, or **processing orders, **PostgreSQL stored procedures offer the flexibility and performance needed to streamline our operations.