Introduction to PostgreSQL DROP PROCEDURE statement

Thedrop procedure statement deletes one or morestored procedures from a database.

The following illustrates the syntax of thedrop procedure statement:

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

In this syntax:

• First, specify the name (procedure_name) of the stored procedure that you want to remove after thedrop procedure keywords.
• Second, use theif 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 thecascade option to drop a stored procedure and its dependent objects, the objects that depend on those objects, and so on. The default option isrestrict 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-separated list of stored procedure names after thedrop procedure keyword like this:

drop procedure [if exists] name1, name2, ...;

Creating sample stored procedures

Let’s create a couple of stored procedures that manage actors so that you can learn how to drop them:

The followinginsert_actor() stored procedureinserts a new row into theactor table. It accepts two arguments which are the first name and last name of the actor.

create or replace procedure insert_actor(fnamevarchar,lnamevarchar)language plpgsqlas $$begininsert into actor(first_name, last_name)values('John','Doe');end;$$;

The followinginsert_actor stored procedure also inserts a row into theactor table. However, it accepts one argument which is the full name of the actor. Theinsert_actor() uses thesplit_part() function to split the full name into first name and last name before inserting them into theactor table.

create or replace procedure insert_actor(full_namevarchar)language plpgsqlas $$declarefnamevarchar;lnamevarchar;begin-- split the fullname into first & last nameselectsplit_part(full_name,' ',1),split_part(full_name,' ',2)into fname,     lname;-- insert first & last name into the actor tableinsert into actor(first_name, last_name)values(fname, lname);end;$$;

The following stored proceduredeletes an actor by id:

create or replace procedure delete_actor(p_actor_idint)language plpgsqlas $$begindelete from actorwhere actor_id= p_actor_id;end;$$;

The following stored procedureupdates the first name and last name of an actor:

create or replace procedure update_actor(p_actor_idint,fnamevarchar,lnamevarchar)language plpgsqlas $$beginupdate actorset first_name= fname,    last_name= lnamewhere actor_id= p_actor_id;end;$$;

PostgreSQL Drop Procedure examples

First, attempt to drop theinsert_actor stored procedure:

drop procedure insert_actor;

PostgreSQL issued the following error:

ERROR:  procedure name "insert_actor" is not uniqueHINT:  Specify the argument list to select the procedure unambiguously.SQL state: 42725

Because there are twoinsert_actor stored procedures, you need to specify the argument list so that PostgreSQL can select the right stored procedure to drop.

Second, drop theinsert_actor(varchar) stored procedure that accepts one argument:

drop procedure insert_actor(varchar);

Since theinsert_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, remove two stored procedures using a singledrop procedure statement:

drop proceduredelete_actor,update_actor;

Summary

• Use thedrop procedure statement to remove a stored procedure.
• Specify a comma-separated list of stored procedure names after thedrop 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.

On this page

• Introduction to PostgreSQL DROP PROCEDURE statement
• Creating sample stored procedures
• PostgreSQL Drop Procedure examples
• Summary