Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What are Stored Procedures?
3.
How to Create and Use Stored Procedures In Postgresql?
3.1.
Syntax: 
3.2.
 
3.3.
Understanding the Syntax: 
3.4.
Parameters
4.
Examples
4.1.
Example 1
5.
Example 2
6.
Frequently Asked Questions
6.1.
What is the main difference between stored procedures and functions?
6.2.
Can I pass parameters to a procedure in PostgreSQL?
6.3.
What do you understand by a primary key?
7.
Conclusion
Last Updated: Mar 27, 2024

How to Create Stored Procedures In PostgreSQL?

Author Lokesh Sharma
1 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

PostgreSQL(PSQL) is a powerful, open-source database that helps us to store data efficiently. Efficient storage and easy access of data is a key factor for developing good applications. We can do this with the help of stored procedures. 

This article will discuss how we can create stored procedures in PostgreSQL. So let us start by understanding stored procedures.

create stored procedures in postgre sql

What are Stored Procedures?

Do you remember creating functions in Java or any other programming language? We created functions to reuse the same piece of code and its functionality. For the same reason, we create stored procedures in SQL. A stored procedure is a prewritten SQL code saved in the database so we can use it repeatedly. Basically, we write an SQL query, save it and then call it to execute it.

However, stored procedures have one advantage over user-defined functions. These functions cannot execute transactions, such as rollback, commit, etc. This feature is present in stored procedures, which makes them very useful. Refer to the stored procedures article for more information about stored procedures.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

How to Create and Use Stored Procedures In Postgresql?

Let us understand the syntax and method to create a stored procedure in PSQL.

Syntax: 

create procedure <name of procedure>(parameter-list)
language plpgsql
as 
$$
declare
-- variable declaration
begin
-- stored procedure body
end; 
$$

 

Understanding the Syntax: 

A stored procedure is created using the create procedure keyword. The name of the procedure is provided along with this keyword. The stored procedure may accept zero or more parameters. 

In the following line, we specify the procedural language to be used. In our case, it is plpgsql (procedural language for PostgreSQL). 

Finally, we define the procedure's body inside double dollars ($$).

Parameters

As mentioned above, the procedure can take zero or more parameters. There are three types of parameters based on their return mode: ininout, and out. However, procedures in PSQL can only accept in and inout parameter mode. Since stored procedures do not return a value, their parameters cannot have out mode.

However, if we want to stop the stored procedure immediately, we can use the return statement without any expression. 

return;

Once we create a stored procedure, we can run it using the call keyword. Check out the syntax below: 

call <stored_procedure_name>(parameter-list);

Examples

Let us consider some examples to understand these procedures.

Example 1

Suppose Coding Ninjas needs to add information about every new ninja that buys its course. It has a database that stores this information. We will create a procedure to add a row containing the student information to the ninjas table. 

Let us first create the table ninjas and add some general entries.

Code: 

drop table if exists ninjas;

create table ninjas (
    id int,
    name varchar(100) not null,
    course_name varchar(100) not null,
    phone_num varchar(12) not null,
    primary key(id)
);


insert into ninjas(name, course_name, phone_num)
values('Lokesh', ‘DSA’, ‘987654320’);

insert into ninjas(name, course_name, phone_num)
values('Varun', ‘Beginners_Java’, ‘9876542310’);

 

You should see the following output if you type select * from ninjas.

example image 1

We have created the table ninjas and added some entries. However, there are thousands of students registering with coding ninjas every day. Hence, typing the above code for insertion time and again is very inefficient.

Hence, we will work smart and create a procedure to add the data easily.

Code:

create procedure add_ninja(
   name varchar(100),
   cname varchar(100),
   pno varchar(12)
)
language plpgsql    
as 
$$
begin
    insert into ninjas(name, course_name, phone_num)
    values(name, cname, pno);
end;
$$

 

We have created the procedure. Now let us see how to use it.

call add_ninja('prathmesh', 'android_Dev', '1234567890');

 

The above statement will call the add_ninja procedure, which adds the student “prathmesh” to the ninjas table. 

Now, if we run the select * from ninjas statement again, you will see the following output.

example image 2

See! How easily we have added a new entry just by typing a single sentence. This is the beauty of using procedures. We can add as many entries as we want using the call statement.

Example 2

Now, let us consider another example that deals with an essential part of using a database: transactions. 

Suppose a bank has a table storing information about account holders and their balances. Suppose the bank wants to perform a particular transaction in which a certain amount is debited from one account and credited to another. Let us see how we can use procedures to ease up our work.

We will start by creating a table and adding some general information.

Code:

drop table if exists acc_holders;

create table acc_holders (
    id int generated by default as identity,
    hname varchar(50) not null,
    balance dec(15, 2) not null,
    primary key(id)
);

insert into acc_holders(hname, balance)
values('Lokesh', 15000);

insert into acc_holders(hname, balance)
values('Riya', 0);

select * from acc_holders;

 

Output:

output image

We have created our table. Now it is time to code the procedure.

Code: 

create procedure trans_amt(
   sender_id int,
   receiver_id int, 
   transaction_amt dec
)
language plpgsql    
as 
$$
begin
    
    update acc_holders 
    set balance = balance - transaction_amt 
    where id = sender_id;


    update acc_holders
    set balance = balance + transaction_amt 
    where id = receiver_id;


    commit;
end;
$$

 

We have created a stored procedure with the name trans_amt. This procedure transfers the given amount from one account to another based on the given ids. 

Let us call this procedure using the call keyword.

call trans_amt(1, 2, 15000);

 

When we run this call statement, it calls the trans_amt procedure, which completes the transaction. 

If you now take a look at the table by running the select * from acc_holders; you will find that the transaction has occurred successfully.

Output: 

output 2 image

Frequently Asked Questions

What is the main difference between stored procedures and functions?

The main difference between stored procedures and functions is that a function must return a value. However, it is optional to return a value in the case of stored procedures. A stored procedure can return zero or more values. 

Can I pass parameters to a procedure in PostgreSQL?

Yes, you can pass parameters to a procedure in PostgreSQL using the IN keyword in the procedure definition and specifying the values for the parameters when calling the procedure. 

What do you understand by a primary key?

A primary key in a table is a group of attributes uniquely identifying an element. We can only define a single primary key of a table. It must be unique and never null.

 

Read Also -  Difference between argument and parameter

Conclusion

In this article, we discussed stored procedures in PostgreSQL and their uses. A stored procedure is a prewritten SQL code saved in the database so we can use it repeatedly. Basically, we write an SQL query, save it and then call it to execute it.

We hope you enjoyed reading this article. If you wish to learn more about SQL, refer to the following blogs. 

Visit our website to read more such blogs. Make sure you enroll in our other courses as well. You can take mock testssolve problems, and interview puzzles. Also, you can check out some exciting interview stuff- interview experiences and an interview bundle for placement preparations. Do upvote our blog to help fellow ninjas grow.

Keep Grinding! 🦾

Happy Coding! 💻

Previous article
What are Dollar Quoted String Constants and Block Structure in Postgresql?
Next article
Working With Triggers In PostgreSQL
Live masterclass