Imagine a university needs to perform several operations when a student submits a fee payment:
Instead of executing these SQL statements separately every time, we can group them into a reusable database program.
PostgreSQL provides Stored Procedures for this purpose.
A Stored Procedure is a reusable block of SQL and procedural code stored inside a database.
It can perform one or more database operations, such as:
Unlike a function, a procedure does not require a return value.
Stored procedures are useful because they:
Both functions and procedures allow us to write reusable database code, but they have different purposes.
| Function | Stored Procedure |
|---|---|
Created using CREATE FUNCTION |
Created using CREATE PROCEDURE |
Called using SELECT or within SQL expressions |
Called using CALL |
| Must declare a return type | Does not require a RETURNS clause |
| Returns a value or result set | Performs operations and can use output parameters |
| Can be used inside SELECT queries | Cannot be used directly as a SELECT expression |
| Cannot independently manage transactions using COMMIT or ROLLBACK | Can manage transactions in permitted CALL contexts |
Remember:
Let’s create a table named students.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department VARCHAR(50),
marks INT
);
Insert sample records:
INSERT INTO students
VALUES
(1, 'Ali', 'Computer Science', 85),
(2, 'Sara', 'Mathematics', 72),
(3, 'Ahmed', 'Computer Science', 65),
(4, 'Usman', 'Physics', 45);
Display the records:
SELECT * FROM students;
Let’s create a simple procedure that displays a message using PostgreSQL’s RAISE NOTICE.
Syntax:
CREATE PROCEDURE procedure_name()
LANGUAGE plpgsql
AS $$
BEGIN
-- SQL statements
END;
$$;
Example:
CREATE PROCEDURE welcome_message()
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Welcome to PostgreSQL';
END;
$$;
Understand the code:
| Keyword | Purpose |
|---|---|
CREATE PROCEDURE |
Creates a procedure |
welcome_message() |
Procedure name |
LANGUAGE plpgsql |
Specifies the procedural language |
BEGIN |
Starts the procedure body |
RAISE NOTICE |
Displays an informational message |
END |
Ends the procedure body |
We execute a stored procedure using the CALL command.
Syntax:
CALL procedure_name();
Example:
CALL welcome_message();
Expected message:
NOTICE: Welcome to PostgreSQL
Unlike a function, we do not call a procedure using SELECT.
A procedure can accept input values through parameters.
For example, let’s create a procedure that displays a student’s name.
CREATE PROCEDURE show_student(
p_student_name VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Student Name: %', p_student_name;
END;
$$;
Call the procedure:
CALL show_student('Ali');
Expected message:
NOTICE: Student Name: Ali
Understand the code:
p_student_name is an input parameter.VARCHAR defines its data type.CALL executes the procedure.'Ali' is passed as the input value.% is a placeholder in the RAISE NOTICE message.Now let’s create a practical procedure that inserts a new student into the table.
CREATE PROCEDURE add_student(
p_student_id INT,
p_student_name VARCHAR,
p_department VARCHAR,
p_marks INT
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO students (
student_id,
student_name,
department,
marks
)
VALUES (
p_student_id,
p_student_name,
p_department,
p_marks
);
RAISE NOTICE 'Student record inserted successfully';
END;
$$;
Execute the procedure:
CALL add_student(
5,
'Ayesha',
'Mathematics',
90
);
Check the table:
SELECT * FROM students;
Ayesha’s record will now appear in the students table.
Important: If the student ID already exists, the primary key constraint will prevent the duplicate record.
Suppose a student’s marks need to be updated.
We can create a procedure for this operation.
CREATE PROCEDURE update_student_marks(
p_student_id INT,
p_new_marks INT
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE students
SET marks = p_new_marks
WHERE student_id = p_student_id;
RAISE NOTICE 'Student marks updated';
END;
$$;
Call the procedure:
CALL update_student_marks(1, 95);
Check the updated record:
SELECT *
FROM students
WHERE student_id = 1;
Ali’s marks will now be 95.
Procedures can use conditional statements.
For example, let’s create a procedure that checks whether a student has passed or failed.
CREATE PROCEDURE check_student_result(
p_marks INT
)
LANGUAGE plpgsql
AS $$
BEGIN
IF p_marks >= 50 THEN
RAISE NOTICE 'Result: Pass';
ELSE
RAISE NOTICE 'Result: Fail';
END IF;
END;
$$;
Call the procedure:
CALL check_student_result(75);
Output:
NOTICE: Result: Pass
Try another example:
CALL check_student_result(40);
Output:
NOTICE: Result: Fail
We can also create a procedure to delete a student record.
CREATE PROCEDURE delete_student(
p_student_id INT
)
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM students
WHERE student_id = p_student_id;
RAISE NOTICE 'Delete operation completed';
END;
$$;
Call the procedure:
CALL delete_student(5);
Check the records:
SELECT * FROM students;
The student with ID 5 will be deleted.
Note: In a real application, it is useful to check whether the student exists before deleting the record.
PostgreSQL procedures support three important parameter modes.
| Parameter | Purpose |
|---|---|
| IN | Receives an input value |
| OUT | Returns an output value |
| INOUT | Receives an input value and returns an updated value |
An IN parameter receives a value.
CREATE PROCEDURE display_marks(
IN p_marks INT
)
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Marks: %', p_marks;
END;
$$;
Execute:
CALL display_marks(85);
An OUT parameter returns a value from a procedure.
CREATE PROCEDURE get_total_students(
OUT total INT
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*)
INTO total
FROM students;
END;
$$;
Execute:
CALL get_total_students(NULL);
The result contains the total number of student records.
An INOUT parameter receives an initial value and returns the modified value.
Example: Increase a number by 10.
CREATE PROCEDURE increase_number(
INOUT num INT
)
LANGUAGE plpgsql
AS $$
BEGIN
num := num + 10;
END;
$$;
Execute:
CALL increase_number(20);
Expected output:
| num |
|---|
| 30 |
We can modify an existing procedure using CREATE OR REPLACE PROCEDURE.
Example:
CREATE OR REPLACE PROCEDURE welcome_message()
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Welcome to Ghazi University';
END;
$$;
Execute the updated procedure:
CALL welcome_message();
We can delete a procedure using DROP PROCEDURE.
Syntax:
DROP PROCEDURE procedure_name(parameter_types);
Example:
DROP PROCEDURE welcome_message();
To avoid an error if the procedure does not exist:
DROP PROCEDURE IF EXISTS welcome_message();
For procedures with parameters, specify their data types.
DROP PROCEDURE IF EXISTS show_student(VARCHAR);
One important feature of PostgreSQL procedures is that they can manage transactions in permitted execution contexts.
For example, a procedure can contain:
COMMIT;
or:
ROLLBACK;
However, transaction control is restricted. A procedure generally cannot execute transaction-control commands when called inside an explicit transaction block or when other restrictions apply.
For beginners, remember:
We will study practical transaction handling in more detail separately.
$$ delimiters.END;.Use the students table created in this lesson.
welcome_message() that displays a welcome message.| Command / Keyword | Purpose |
|---|---|
CREATE PROCEDURE |
Creates a stored procedure |
CALL |
Executes a procedure |
IN |
Receives input |
OUT |
Returns output |
INOUT |
Receives and returns a value |
CREATE OR REPLACE PROCEDURE |
Creates or updates a procedure |
DROP PROCEDURE |
Deletes a procedure |
RAISE NOTICE |
Displays an informational message |
Remember: Stored procedures allow us to save and execute reusable database operations using the CALL command.
Triggers in PostgreSQL
You will learn how to execute database actions automatically when records are inserted, updated, or deleted.