PostgreSQL – Drop Procedure

In PostgreSQL, the drop procedure statement removes a stored procedure.

Syntax:
drop procedure [if exists] procedure_name (argument_list)
[cascade | restrict]

Let’s analyze the above syntax:

• First, specify the name (procedure_name) of the stored procedure that you want to remove after the drop procedure keywords.
• Second, use the if exists option if you want PostgreSQL to issue a notice instead of an error if you drop a stored procedure that does not exist.
• Third, specify the argument list of the stored procedure if the stored procedure’s name is not unique in the database. Note that stored procedures that have different argument lists can share the same name. PostgreSQL needs the argument list to determine which stored procedure that you want to remove.
• Finally, use the cascade option to drop the stored procedures and its dependent objects and the objects that depend on those objects and so on. The default option is restricted that will reject the removal of the stored procedure in case it has any 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, ...;

For the sake of example, we will create a stored procedure on the sample database ie, dvdrental.

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('John', 'Doe');
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
    -- split the fullname into first & last name
    select 
        split_part(full_name, ' ', 1),
        split_part(full_name, ' ', 2)
    into fname,
         lname;
    
    -- insert first & last name into the actor table
    insert into actor(first_name, last_name)
    values('John', 'Doe');
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; 
$$;

Example:

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);

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;

Conclusion:

• 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.