PostgreSQL allows the user to create tables and perform queries to fetch data from these tables and gain useful insights using the data. PostgreSQL allows the use of built-in functions as well as the creation of new functions and procedures to manage PostgreSQL tables. Functions and stored Procedures serve almost similar functionalities but there are a few differences between them.

This guide will explain the difference between Procedure and Function in PostgreSQL using examples.

Procedure Vs Function in PostgreSQL

Procedures are a set of instructions that take a certain input and apply actions on it which are provided in its body. The procedure does not return any value on calling it however it performs the action and updates the values in the said table. Whereas, the function also takes input and performs some tasks to return the value while executing the calling query.

The Procedures can’t be called with other SQL queries whereas the function can be called within SQL queries to get the value in return. The procedure is compiled once and can be called many times to perform multiple tasks in order.

Create PostgreSQL Table

Use the following code to create a table in the PostgreSQL database to use Procedure and Function on it:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary DECIMAL(10, 2),
  department_id INT
   );

The above code creates a table named employees with multiple fields such as id, name, age, salary, and department_id. Each field has its data type according to the data to be stored in it and the id is set to be the primary key of the table:

Use the following code to insert data in the above-created table:

INSERT INTO employees (name, age, salary, department_id)
   VALUES ('AB De-Villiers', 30, 5000.00, 1),
  ('Steve Smith', 28, 6000.00, 1),
  ('Michael Johnson', 35, 8000.00, 2),
  ('Ollie Robinson', 32, 5500.00, 2);

The output snippet clarifies that four records have been inserted into the selected table:

Use the following command to fetch the data stored in the employees' table:

SELECT * FROM employees;

The above code displays the data available in the "employees" table:

How to Use a Procedure in PostgreSQL?

After creating and inserting data in the PostgreSQL table, create a procedure to increase the salary of the employee:

CREATE OR REPLACE PROCEDURE increase_salary_by_percentage(
  IN percentage DECIMAL(5, 2)
   )
 LANGUAGE plpgsql
 AS $$
 BEGIN
  UPDATE employees
  SET salary = salary + (salary * (percentage / 100));
 END;
   $$;

The above code creates a procedure named “increase_salary_by_percentage” using the SQL procedural language. The body of the procedure suggests that on calling it, the procedure should update the salary of the employee and adds a specific percentage of the current salary:

Simply call the stored procedure by specifying the increment percentage as an argument:

CALL increase_salary_by_percentage(10.0);

The above code increases the salary of the employee by 10 percent as provided in the argument while calling the procedure:

Use the following code to get the updated table of PostgreSQL:

SELECT * FROM employees;

The following screenshot displays the increased salary by 10 percent which happened after calling the procedure:

How to Use a Function in PostgreSQL?

Create a function in PostgreSQL by using the following code:

CREATE OR REPLACE FUNCTION get_average_salary()
 RETURNS DECIMAL(10, 2)
 LANGUAGE plpgsql
 AS $$
 DECLARE
  average_salary DECIMAL(10, 2);
 BEGIN
  SELECT AVG(salary) INTO average_salary
  FROM employees;
 RETURN average_salary;
 END;
   $$;

The above code creates a function named get_average_salary() which returns the value in decimal format using the SQL language. The body of the function suggests that the function will return the average salary from the employees' table:

Use the following code to call the function:

SELECT department_id, get_average_salary() AS average_salary
 FROM employees
 GROUP BY department_id;

The above code fetches the data from the department_id to apply the function on the employees' table and then uses GROUP BY clause on the department_id. Running the above code displayed the average salary of each department on calling the PostgreSQL function:

That’s all about Procedure and Functions in PostgreSQL and it has been explained with examples.

Conclusion

The major difference between a Procedure and a Function in PostgreSQL is that the Procedure does not return the value and the function does return it. The Procedures can’t be called with other SQL queries whereas the function can be called within SQL queries to get the value in return. This post demonstrated the difference between a Procedure and a Function in PostgreSQL using examples.