PostgreSQL - Stored Procedures

A procedure (also known as stored procedure) is a collection of SQL statements wrapped within the CREATE PROCEDURE statement. A procedure always contains a name, parameter lists, and SQL statements. It may also contain a conditional statement like IF or CASE or the Loops. A procedure can be invoked by using procedures, other procedures and applications.

Stored Procedure Features

• Stored Procedure increases the performance of the applications. Once stored procedures are created, they are compiled and stored in the database.
• Stored procedure reduces the traffic between application and database server. Because the application has to send only the stored procedure's name and parameters instead of sending multiple SQL statements.
• The stored procedure are reusable. A business logic can be implemented within a stored procedure which can be used by applications multiple times, or can be used by different modules of an application. This makes the database more consistent. If any change is required, it can be made in the stored procedure only.
• The stored procedures are more secure than the AdHoc queries. The permission can be granted to the user to execute the stored procedure without giving permission to the tables used in the stored procedure. The stored procedure helps to prevent the database from SQL Injection.

CREATE PROCEDURE

The CREATE PROCEDURE statement is used to create a stored procedure. The CREATE PROCEDURE statement requires the CREATE ROUTINE privilege for it.

By default, a stored routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.procedure_name at the time of creation.

The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case-sensitive.

The stored procedure can also execute another stored procedure or a function that modularizes the code.

The syntax of using CREATE PROCEDURE statement in PostgreSQL is given below:

CREATE [OR REPLACE] PROCEDURE procedure_name 
  ([parameter datatype [,...] ])

LANGUAGE SQL  

AS $$  

  sql_body

$$;

Parameters

To invoke a stored procedure, the CALL statement is used. See the syntax below:

CALL procedure_name(parameter(s));

Create Procedure without Parameter

Consider a database table called Employee with the following records:

 EmpID | Name   | City      | Age | Salary 
-------+--------+-----------+-----+--------
 1     | John   | London    | 25  | 3000   
 2     | Marry  | New York  | 24  | 2750   
 3     | Jo     | Paris     | 27  | 2800   
 4     | Kim    | Amsterdam | 30  | 3100   
 5     | Ramesh | New Delhi | 28  | 3000   
 6     | Huang  | Beijing   | 28  | 2800   

To fetch all records of this table where Salary is greater than or equal to 3000 and count all the table rows, the following code can be used which creates a procedure named high_salary_employees:

CREATE PROCEDURE high_salary_employees()

LANGUAGE SQL  
AS $$    
  
  SELECT * FROM Employee WHERE Salary >= 3000;  
  SELECT COUNT(EmpID) AS TotalEmployee FROM Employee;  
 
$$;

After successful execution, the procedure can be called as follows:

CALL high_salary_employees();  

This will produce the result as shown below:

 EmpID | Name   | City      | Age | Salary 
-------+--------+-----------+-----+--------
 1     | John   | London    | 25  | 3000   
 4     | Kim    | Amsterdam | 30  | 3100   
 5     | Ramesh | New Delhi | 28  | 3000   

 TotalEmployee 
---------------
 6             

Create Procedure with Parameter

In this procedure, the parameter named 'var1' of integer type is used to take the value from user. Its body part fetches the records from the table using a SELECT statement and returns only those rows that is provided by user. It also returns the total number of rows of the specified table.

CREATE PROCEDURE get_employees(var1 integer)

LANGUAGE SQL  
AS $$    
  
  SELECT * FROM Employee LIMIT var1;  
  SELECT COUNT(EmpID) AS TotalEmployee FROM Employee;
 
$$;

After successful execution, the procedure can be called as follows:

CALL get_employees(3);  

This will produce the result as shown below:

 EmpID | Name  | City     | Age | Salary 
-------+-------+----------+-----+--------
 1     | John  | London   | 25  | 3000   
 2     | Marry | New York | 24  | 2750   
 3     | Jo    | Paris    | 27  | 2800   

 TotalEmployee 
---------------
 6             

Drop Procedure

Once a procedure is created in PostgreSQL, it can be removed by using DROP PROCEDURE statement. To execute this statement, the user must be the owner of the procedure.

Syntax

The syntax of removing a procedure in PostgreSQL is given below:

DROP PROCEDURE [IF EXISTS] procedure_name; 

The IF EXISTS is an optional parameter that conditionally removes procedure only if it exists on the database. If a procedure is deleted which does not exist, it will raise an error.

Example:

To drop a procedure named get_employees, the following statement can be used:

DROP PROCEDURE get_employees;