How to create Stored Procedures in PostgreSQL with example?

Sumit Talwar · · 927 Views
How to create Stored Procedures in PostgreSQL with example?

In this post, you will learn how to use the CREATE PROCEDURE statement to create new stored procedures in PostgreSQL.

A stored procedure is a bunch of Structured Query Language (SQL) statements with an assigned name, which is stored in a relational database management system as a group, so it tends to be reused. Additionally, the stored procedure doesn't need to have an output.

To define a new stored procedure, you use the create procedure statement. The following shows the basic syntax of the create procedure statement:

create [or replace] procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$

In this syntax:

  1. To begin with, specify the name of the stored procedure after the create procedure keywords.

  2. Second, define parameters for the stored procedure. A stored procedure can accept zero or more parameters.

  3. Third, specify plpgsql as the procedural language for the stored procedure. Note that you can use other procedural languages for the stored procedure such as SQL, C, etc.

  4. At last, use the dollar-quoted string constant syntax to define the body of the stored procedure.

Parameters in stored procedures can have the in and inout modes. But they can't have the out mode. A stored procedure doesn't a value. You can't utilize the return statement with a value inside a store procedure like this:

return expression;

However, you can utilize the return statement without the expression to stop the stored procedure right away:

return;

If you need to return a value from a stored procedure, you can use parameters with the inout mode.

PostgreSQL CREATE PROCEDURE statement examples

Here, I will use an 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('Bob',10000);

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

The following statement shows the data from the accounts table:

select * from accounts;
How to create Stored Procedures in PostgreSQL with example?

The following example 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);

For example, this statement invokes the transfer stored procedure to transfer $1,000 from Bob’s account to Alice’s account.

call transfer(1,2,1000);

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

SELECT * FROM accounts;
How to create Stored Procedures in PostgreSQL with example?

It worked as expected.

Summary

  1. Use create procedure statement to define a new stored procedure.

  2. Use the call statement to invoke a stored procedure.

0

Please login or create new account to add your comment.

0 comments
You may also like:

Install phpMyAdmin Manually with Nginx server on Ubuntu

In this guide, I will show you how to install and configure phpMyAdmin with Nginx, MySQL, and PHP8.0 (LEMP) on an Ubuntu system. phpMyAdmin is a free and open-source database (...)
Harish Kumar

How to Deploy A Laravel Application to Heroku with Database?

In this post, I'll show you the best way to deploy your current Laravel application from local to Heroku with the Postgres database. It's quick and easy.
Harish Kumar

How to connect a PostgreSQL database server using PHP PDO?

Before connecting with the PostgreSQL database using PHP PDO, make sure PHP PDO PostgreSQL driver enabled.
Razet

How to create PHP Pagination using PDO with example?

In this example, I am going to show you how to create pagination in PHP using PDO.First, create a DatabaseConnection.php to create a database connection.
Razet

What Is WP_Query & WordPress query functions?

As WordPress developers, we regularly need to fetch posts, pages, and other content according to specific criteria from the WordPress database. Typically, we don't have to create (...)
Harish Kumar