PostgreSQL - Drop Procedure
Last Updated :
08 Aug, 2024
In PostgreSQL, the DROP PROCEDURE statement is used to remove a stored procedure from the database. Let us look at the syntax, usage, and examples of the DROP PROCEDURE statement, along with additional tips to enhance your understanding and improve database management.
The DROP PROCEDURE statement in PostgreSQL allows you to delete stored procedures that are no longer needed. Properly managing and removing unnecessary stored procedures can help maintain database performance and organization.
Syntax
DROP PROCEDURE [IF EXISTS] procedure_name (argument_list)
[CASCADE | RESTRICT];
Parameters
Let's analyze the above syntax:
- procedure_name: Specify the name of the stored procedure you want to remove.
- IF EXISTS: Use this option to avoid errors if the procedure does not exist; PostgreSQL will issue a notice instead.
- argument_list: Specify the argument list if the procedure name is not unique. Different procedures can share the same name but have different argument lists.
- CASCADE | RESTRICT:
- CASCADE: Drops the procedure and its dependent objects.
- RESTRICT: Default option; prevents dropping the procedure if it has dependent objects.
To drop multiple stored procedures, you specify a comma-list of stored procedure names after the drop procedure keyword like this:
Syntax: drop procedure [if exists] name1, name2, ...;
PostgreSQL Drop Procedure Statement
For the sake of example, we will create a stored procedure on the sample database ie, dvdrental.
Example 1: Creating and Dropping Stored Procedures
Let’s create a couple of stored procedures that manage actors so that you can learn how to drop them:

The following 'insert_actor()' stored procedure inserts a new row into the actor table. It accepts two arguments which are the first name and last name of the actor.
CREATE OR REPLACE PROCEDURE insert_actor(
fname VARCHAR,
lname VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO actor(first_name, last_name)
VALUES(fname, lname);
END;
$$;
The following 'insert_actor' stored procedure also inserts a row into the actor table. However, it accepts one argument which is the full name of the actor. The 'insert_actor()' uses the 'split_part()' function to split the full name into first name and last name before inserting them into the actor table.
CREATE OR REPLACE PROCEDURE insert_actor(
full_name VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
fname VARCHAR;
lname VARCHAR;
BEGIN
SELECT
SPLIT_PART(full_name, ' ', 1),
SPLIT_PART(full_name, ' ', 2)
INTO fname,
lname;
INSERT INTO actor(first_name, last_name)
VALUES(fname, lname);
END;
$$;
The following stored procedure deletes an actor by id:
CREATE OR REPLACE PROCEDURE delete_actor(
p_actor_id INT
)
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM actor
WHERE actor_id = p_actor_id;
END;
$$;
And the following stored procedure updates the first name and last name of an actor:
CREATE OR REPLACE PROCEDURE update_actor(
p_actor_id INT,
fname VARCHAR,
lname VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE actor
SET first_name = fname,
last_name = lname
WHERE actor_id = p_actor_id;
END;
$$;
Dropping Stored Procedures
First, attempt to drop the 'insert_actor' stored procedure:
DROP PROCEDURE insert_actor;
Output:

Because there are two 'insert_actor' stored procedures, you need to specify the argument list so that PostgreSQL can select the right stored procedure to drop.
Second, drop the 'insert_actor(varchar)' stored procedure that accepts one argument:
DROP PROCEDURE insert_actor(VARCHAR);
Example 2: Detailed Steps to Drop Procedures
Since the 'insert_actor' stored procedure is unique now, you can drop it without specifying the argument list:
DROP PROCEDURE insert_actor;
It is the same as:
DROP PROCEDURE insert_actor(varchar, varchar);
Third, drop two stored procedures using a single drop procedure statement:
DROP PROCEDURE
delete_actor,
update_actor;
Best Practices for Using DROP PROCEDURE
- Use the drop procedure statement to remove a stored procedure.
- Specify a comma-separated list of stored procedure names after the drop procedure keywords to drop multiple stored procedures.
- If the stored procedure name is not unique, use the argument list to specify which stored procedure you want to drop.
Similar Reads
PostgreSQL - CREATE PROCEDURE PostgreSQL CREATE PROCEDURE allows developers to define stored procedures that apply complex business logic, transaction handling and multiple SQL statements in a reusable manner. Unlike functions, PostgreSQL stored procedures can manage transactions with commands like COMMIT and ROLLBACK. Understan
4 min read
PostgreSQL - DROP ROLE In PostgreSQL, the DROP ROLE statement is used to remove a role from the database. Let us look at the process of dropping a role, including important considerations and steps to ensure smooth execution.Syntax The basic syntax of the DROP ROLE Statement in PostgreSQL is:DROP ROLE [IF EXISTS] target_r
3 min read
PostgreSQL - DROP TRIGGER The DROP TRIGGER statement in PostgreSQL is essential for managing database triggers effectively. Triggers in PostgreSQL are database callbacks that automatically execute functions in response to certain events, such as INSERT, UPDATE, or DELETE. DROP TRIGGER provides database administrators and dev
4 min read
PostgreSQL - DROP SCHEMA PostgreSQL offers a powerful schema management system, allowing database administrators to organize and manage objects within specific schemas. The DROP SCHEMA statement in PostgreSQL provides a straightforward way to delete entire schemas and the associated objects, making it a valuable tool for da
4 min read
PostgreSQL - DROP TABLESPACE In PostgreSQL, the DROP TABLESPACE statement is used to remove a tablespace. A tablespace is a storage location where PostgreSQL stores database objects such as tables and indexes. This command is useful for managing and cleaning up database storage.Let us get a better understanding of the DROP TABL
2 min read