Introduction to PostgreSQL DROP FUNCTION statement

To remove a user-defined function, you use thedrop function statement.

Here’s the syntax of thedrop function statement:

drop function [if exists] function_name(argument_list)[cascade | restrict]

In this syntax:

• First, specify the name of the function that you want to remove after thedrop function keywords.
• Second, use theif exists option if you want to instruct PostgreSQL to issue a notice instead of an error if the function does not exist.
• Third, specify the argument list of the function. Sincefunctions can be overloaded, PostgreSQL needs to know which function you want to remove by checking the argument list. If a function is unique within the schema, you do not need to specify the argument list.

When a function has any dependent objects such as operators ortriggers, you cannot drop that function.

To drop the function and its dependent objects, you can use thecascade option. Thedrop function with thecascade option will recursively remove the function, its dependent objects, and the objects that depend on those objects, and so on.

By default, thedrop function statement uses therestrict option that rejects the removal of a function when it has any dependent objects.

To drop multiple functions using a singledrop function statement, you specify a comma-separated list of function names after thedrop function keyword like this:

drop function [if exists] function1, function2, ...;

PostgreSQL Drop Function examples

The following statement uses thecreate function statement to define a function that returns a set of films includingfilm_id,title, andactor:

create or replace function get_film_actors()returns setof recordas $$declare   rec record;begin   for recin selectfilm_id,title,            (first_name|| ' ' || last_name)::varcharfrom filminner join film_actorusing(film_id)inner join actorusing (actor_id)order by titleloop        return next rec;end loop;return;end;$$language plpgsql;

The following statement defines a function with the same nameget_film_actors. However, it accepts a film id as the argument:

create or replace function get_film_actors(p_fiml_idint)returns setof recordas $$declare   rec record;begin   for recin selectfilm_id,title,            (first_name|| ' ' || last_name)::varcharfrom filminner join film_actorusing(film_id)inner join actorusing (actor_id)where film_id= p_fiml_idorder by titleloop        return next rec;end loop;return;end;$$language plpgsql;

The following statement attempts to drop theget_film_actors function:

drop function get_film_actors;

PostgreSQL issued an error:

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

Since theget_film_actors stored procedure is not unique, you need to specify which function you want to drop.

The following statement drops theget_film_actors function that has zero parameters:

drop function get_film_actors();

Now, there is only oneget_film_actors function left. Since it is unique in the database, you can drop it without specifying its argument list like this:

drop function get_film_actors;

Alternatively, if you want to specify the exact function, you can use the function name with the argument list:

drop function get_film_actors(int);

Summary

• Use thedrop function statement to delete a function from a database.
• Specify the argument list in the function if the function is overloaded.
• Use thedrop function statement with thecascade option to drop a function and its dependent objects and objects that depend on those objects, and so on.

Last updated onFebruary 7, 2024

On this page

• Introduction to PostgreSQL DROP FUNCTION statement
• PostgreSQL Drop Function examples
• Summary