PostgreSQL DROP FUNCTION Statement

Summary: In this tutorial, you’ll learn how to use the PostgreSQL DROP FUNCTION statement to drop a function from the database.

Getting Started with the PostgreSQL DROP FUNCTION Statement #

The DROP FUNCTION statement allows you to remove a user-defined function permanently from the database.

Here’s the syntax of the DROP FUNCTION statement:

DROP FUNCTION [IF EXISTS] function_name(parameters);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the function name after the DROP FUNCTION keyword with a list of parameters.
• Second, use the IF EXISTS clause if you want to drop the function conditionally, only if it exists.

If a function has dependent objects like operators and triggers, you cannot drop it.

Fortunately, you can drop the function and its dependent objects by using the CASCADE option explicitly:

DROP FUNCTION [IF EXISTS] function_name(parameters) CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The RESTRICT option prevents you from removing a function with dependent objects. The DROP FUNCTION statement uses the RESTRICT option by default.

To remove multiple functions at once, you can specify a comma-separated list of function names after the DROP FUNCTION keyword:

DROP FUNCTION 
    function_name1(parameter), 
    function_name2(parameter), ...;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL DROP FUNCTION Statement Examples #

Let’s create some functions and demonstrate the DROP FUNCTION statement to remove them.

First, create a function that returns the total inventory quantity:

CREATE OR REPLACE FUNCTION get_total_inventory() 
    RETURNS int 
AS 
$$
    SELECT sum(quantity) FROM inventories;
$$ LANGUAGE sql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create a function that returns the product, warehouse, and inventory quantity within a range:

CREATE OR REPLACE FUNCTION get_inventory(min_qty int, max_qty int) 
RETURNS TABLE (product varchar, warehouse varchar, quantity int) 
AS 
$$
    SELECT product_name, warehouse_name, quantity 
    FROM inventories
    JOIN products USING (product_id) 
    JOIN warehouses USING (warehouse_id)
    WHERE quantity >= min_qty AND quantity <= max_qty;
$$ LANGUAGE sql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, create an overloaded function to return inventory at a specific quantity:

CREATE OR REPLACE FUNCTION get_inventory(qty int) 
RETURNS TABLE (product varchar, warehouse varchar, quantity int) 
AS $$
    SELECT product_name, warehouse_name, quantity 
    FROM inventories
    JOIN products USING (product_id) 
    JOIN warehouses USING (warehouse_id)
    WHERE quantity = qty;
$$ LANGUAGE sql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, create two simple functions that add and subtract two integers:

CREATE OR REPLACE FUNCTION add(a int, b int) 
RETURNS INT 
AS 
$$
    SELECT a + b;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION subtract(a int, b int) 
RETURNS INT 
AS
$$
    SELECT a - b;
$$ LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Basic PostgreSQL DROP FUNCTION statement example #

The following DROP FUNCTION statement removes the get_total_inventory function:

DROP FUNCTION IF EXISTS get_total_inventory;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Since no other functions have the same name as the get_total_inventory function, we don’t need to specify the parameter list.

Dropping an Overloaded Function Example #

The following DROP FUNCTION statement attempts to drop the get_inventory function:

DROP FUNCTION IF EXISTS get_inventory;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

It issues an error because two functions have the same name get_inventory:

function name "get_inventory" is not uniqueCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To fix it, you need to specify the parameter list explicitly so PostgreSQL knows which function you want to drop.

For example, the following statement drops the get_inventory function that accepts an integer (int) parameter:

DROP FUNCTION IF EXISTS get_inventory(int);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Dropping Multiple Functions Example #

The following example uses the DROP FUNCTION statement to drop two functions at once:

DROP FUNCTION add, subtract;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

• Use the PostgreSQL DROP FUNCTION to remove a function from a database.

Quiz #