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.