How to Create Stored Procedures In PostgreSQL?

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: in, inout, 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.

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.

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:

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: 

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. 

• introduction to SQL.
• advance sql.
• pl-sql introduction.
   

Visit our website to read more such blogs. Make sure you enroll in our other courses as well. You can take mock tests, solve 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! 💻