Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql-introduction-to-stored-procedures/ below:

PostgreSQL - Introduction to Stored Procedures

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.

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.

• 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.

• 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.

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.

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

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

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

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

SELECT * FROM accounts;

Output

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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4