Post
Ask Question

How To Create And Use Procedure And Function In PostgreSQL

Introduction

 
PostgreSQL is the most popular object-relational database system. It is a robust, high-performance based database system. In addition, is open source and free. In this article, we will discuss how to use procedures and functions to perform operations, like insert, delete, update, and select.
 

Function

 
In general, a function is a set of SQL statements that carried out any operation, such as select, insert delete, and update. There are two types of functions in PostgreSQL "system-defined functions" and "user-defined functions". In this article, we discuss the user-defined function.
 
Syntax 
  1. CREATE OR REPLACE FUNCTION function_name (parameters-list)  
  2. RETURNS return_type  
  3. LANGUAGE plpgsql  
  4. AS  
  5. $$  
  6.     DECLARE  
  7.     --- variables  
  8.     BEGIN  
  9.         --- SQL statements (logic)  
  10.     END  
  11. $$  
 Listing 1.
 
Advantage
 
You can have multiple SQL statements in a function and you can return any type of results like a table or a single value (integer, varchar, date, timestamp, etc).
 
Limitation
 
You can not use transactions inside the function.
 

Procedure

 
To overcome the limitations of a function, PostgreSQL has a procedure that supports transactions. In the procedure, we can start, commit, rollback the transaction. However, the procedure can not return a result set like a table. It can only return the INOUT parameters.
 
Syntax
  1. CREATE OR REPLACE PROCEDURE procedure_name (parameters-list)  
  2. LANGUAGE plpgsql  
  3. AS  
  4. $$  
  5. DECLARE  
  6. --- Variables  
  7. BEGIN  
  8.   
  9.     --- SQL statements (logic)  
  10.   
  11. END  
  12. $$  
 Listing 2.
 

Create database

 
To create the database, right-click on the databases and Create a Database, as shown in Figure 1.
 
Figure 1.
 
In general, the tab set the name of the database in this demo, In our case, we are using “EmployeeManagementSystem”.
 
 
Figure 2.
 

Create table

 
Let’s create the Employees table in the EmployeeManagementSystem database using the following SQL script as shown in Listing 1. 
  1. CREATE TABLE Employees  
  2. (  
  3.     Id serial,  
  4.     Name VARCHAR(100),  
  5.     DateOfBirth Date,  
  6.     City VARCHAR(100),  
  7.     Designation VARCHAR(100),  
  8.     JoiningDate Date  
  9. )  
Listing 3. 
 
Click the “Run” button to execute the above script as shown in figure 3.
 
 

Parameters Type

 
Before creating a procedure and function, let’s discuss the type of the parameters, there are three types of parameters, we can use an in function and procedure:
  • IN
  • OUT
  • INOUT

IN

IN represents an input type parameter. It is used to pass the value in the function or procedure, by default all the parameters are input type if we don’t use IN keyword after the parameter name.
 
OUT
 
OUT represents the output type parameters. It returns the value; you can pass it as null or it can be uninitialized because these types of a parameter only use to set and return value from function and procedure
 
INOUT
 
INOUT represent both input and output type parameter, these types of parameters can be used to pass the value as well as return the value from a function or procedure.
 

Create Procedure

 
Use the below script to create a procedure with the name “AddEmployee”. This will insert the employee information in the employee table. 
  1. CREATE OR REPLACE PROCEDURE AddEmployee  
  2. (  
  3.     EmpId INOUT INT,  
  4.     EmpName VARCHAR(100),  
  5.     EmpDob DATE,  
  6.     EmpCity VARCHAR(100),  
  7.     EmpDesignation VARCHAR(100),  
  8.     EmpJoiningDate DATE  
  9. )  
  10. LANGUAGE plpgsql AS  
  11. $$  
  12. BEGIN         
  13.    INSERT INTO Employees (Name,DateOfBirth,City,Designation,JoiningDate) VALUES   
  14.     (EmpName,  
  15.      EmpDob,  
  16.      EmpCity,  
  17.      EmpDesignation,  
  18.      EmpJoiningDate  
  19.     ) RETURNING Id INTO EmpId;  
  20. END  
  21. $$;  
Listing 4.
 
Figure 4.
 
Let’s execute this procedure using the SQL statement as shown in listing 5. It will insert employee records in the employee table.
  1. CALL AddEmployee(null,'Peter Parker','1997-10-01','New York' ,'Web Developer','2020-11-01')  
Listing 5.
 
Figure 5.
 
Now check the table to see the inserted record with the help select statement as shown in listing 6.
  1. SELECT * FROM Employees  
Listing 6.
 
Figure 6.
 
 Now we will create a procedure to update the employee record as shown in listing 7.
  1. CREATE OR REPLACE PROCEDURE UpdateEmployee  
  2. (  
  3.     EmpId INT,  
  4.     EmpName VARCHAR(100),  
  5.     EmpDob DATE,  
  6.     EmpCity VARCHAR(100),  
  7.     EmpDesignation VARCHAR(100),  
  8.     EmpJoiningDate DATE  
  9. )  
  10. LANGUAGE plpgsql AS  
  11. $$  
  12. BEGIN         
  13.    UPDATE Employees SET   
  14.    Name = EmpName,  
  15.    DateOfBirth = EmpDob,  
  16.    City = EmpCity,  
  17.    Designation = EmpDesignation,  
  18.    JoiningDate = EmpJoiningDate  
  19.    Where Id = EmpId;  
  20. END  
  21. $$;  
Listing 7.
 
Figure 7.
 
Follow listing 8, to call the UpdateEmployee procedure that will allow to update employee records. 
  1. CALL UpdateEmployee(1,'Peter S Parker','1999-10-01','New York' ,'Web Developer','2020-11-01')  
Listing 8.
 
Figure 8.
 
We have created procedures for insert and update, now we will create a procedure that will allow us to delete employee records. See Listing 8.
  1. CREATE OR REPLACE PROCEDURE DeleteEmployee  
  2. (  
  3.     EmpId INT  
  4. )  
  5. LANGUAGE plpgsql AS  
  6. $$  
  7. BEGIN  
  8.     DELETE FROM Employees WHERE Id = EmpId;  
  9. END  
  10. $$;  
 Listing 8.
 
 
Figure 9.
 
See listing 9 to call the DeleteEmployee() for employee ID '2'
  1. CALL DeleteEmployee(2)  
  Listing 9.
 
Figure 10.
 
Now it's time to create functions,  Create a function GetAllEmployee() that will return all the employees, see listing 10. 
  1. CREATE OR REPLACE FUNCTION GetAllEmployees()  
  2. RETURNS Employees  
  3. LANGUAGE SQL   
  4. AS   
  5. $$  
  6.     SELECT * FROM Employees;  
  7. $$;  
 Listing 10.
 
 
 
Figure11. 
 
The 'Select' statement will be used to run and get the data from GetAllEmployee() function, as shown in Listing 11. 
  1. SELECT * FROM GetAllEmployees()  
  Listing 11.
 
Figure12.
 
The below function, GetemployeeById(), shown in Listing 12, will return a single row for a particular employee ID.  
  1. CREATE OR REPLACE FUNCTION GetEmployeeById(EmpId INT)  
  2. RETURNS Employees  
  3. LANGUAGE SQL   
  4. AS   
  5. $$  
  6.     SELECT * FROM Employees WHERE Id = EmpId;  
  7. $$;  
  Listing 12.
 
Figure13. 
 
Let's pass the employee Id '3' to get the employee record with the select statement.
  1. SELECT * FROM GetEmployeeById(3)  
  Listing 13.
 
 
Figure14.  
 
As we know, we are storing the "date of birth" of the employee, So let's create a function the will return the age of the employee, In listing 14, we are using the system defined "Age" function that will take two parameters current date and employee date of birth. It will return the difference as the age of the employee.    
  1. CREATE OR REPLACE FUNCTION GetEmployeeAge(EmpId INT, Age OUT VARCHAR(100))  
  2. LANGUAGE plpgsql   
  3. AS   
  4. $$  
  5. BEGIN  
  6.     SELECT AGE(NOW()::Date,DateOfBirth) into Age FROM Employees WHERE Id = EmpId;  
  7. END;  
  8. $$  
 Listing 14.
 
 Figure15.
 
Call the GetEmployeeAge() function for employee ID 1 and it will return 21 years, 3 months, 21 days.
  1. SELECT * FROM GetEmployeeAge(1)  
  Listing 15.
 
 
Figure 16. 
 

Conclusion

 
A function is a good choice to execute an SQL statement that returns a single value result or table formatted result. However, if you want to start a transaction, commit or rollback with multiple SQL statements, then the procedure is the best choice.  


Similar Articles