PostgreSQL – Introduction to Stored Procedures

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 addition, stored procedures also add many procedural features e.g., control structures and complex calculation. These allow you to develop custom functions much easier and more effective.

It is possible to call a Procedural code block using the DO command without defining a function or stored procedure.

PostgreSQL categorizes the procedural languages into two main groups:

Safe languages can be used by any users. SQL and PL/pgSQL are safe languages.
Sand-boxed languages are only used by superusers because sand-boxed languages provide the capability to bypass security and allow access to external sources. C is an example of a sandboxed language.

By default, PostgreSQL supports three procedural languages: SQL, PL/pgSQL, and C. You can also load other procedural languages e.g., Perl, Python, and TCL into PostgreSQL using extensions.

Advantages of using PostgreSQL stored procedures:

The stored procedures bring many advantages as follows:

Reduce the number of round trips between applications and database servers. All SQL statements are wrapped inside a function stored in the PostgreSQL database server so the application only has to issue a function call to get the result back instead of sending multiple SQL statements and wait for the result between each call.
Increase application performance because the user-defined functions and stored procedures are pre-compiled and stored in the PostgreSQL database server.
Reusable in many applications. Once you develop a function, you can reuse it in any applications.

Disadvantages of using PostgreSQL stored procedures:

Besides the advantages of using stored procedures, there are some caveats:

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

Example:

We will use the following accounts table for the demonstration:

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;

That depicts the result as shown below:

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

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

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

SELECT * FROM accounts;

Output:

Article Tags :

PostgreSQL

postgreSQL-stored-procedures