How to Use the DROP FUNCTION Command in PostgreSQL

Abdul Wahab Junaid

3 years ago

How to Use the DROP FUNCTION Command in PostgreSQL

Functions in PostgreSQL are incredibly useful for encapsulating logic you want to reuse across queries, triggers, and applications. But like any other piece of code, functions sometimes need to be removed, whether they’re outdated, replaced by a better implementation, or simply no longer needed. In this article, I’ll cover the DROP FUNCTION command thoroughly, including syntax, handling function overloading, dependency management, and troubleshooting.

A Quick Refresher on Functions

A function in PostgreSQL is a reusable block of code, typically written in SQL, PL/pgSQL, or another supported procedural language, that accepts parameters and returns a result. Functions can be used in queries, called directly, or attached to triggers. Because PostgreSQL supports function overloading, meaning you can have multiple functions with the same name but different parameter types, dropping a function requires being precise about which version you mean.

Basic Syntax of DROP FUNCTION

DROP FUNCTION [IF EXISTS] function_name [ ( [argument_list] ) ] [, ...] [CASCADE | RESTRICT];

Here’s what each part means:

IF EXISTS: prevents an error if the function doesn’t exist.
function_name: the name of the function.
argument_list: the parameter types of the function, needed when the function name is overloaded.
CASCADE: drops any dependent objects, like triggers that use this function.
RESTRICT: the default; refuses to drop if dependent objects exist.

A Simple Example

Suppose you have a straightforward function that calculates a discount:

CREATE FUNCTION calculate_discount(price numeric, discount_percent numeric)
RETURNS numeric AS $$
BEGIN
    RETURN price - (price * discount_percent / 100);
END;
$$ LANGUAGE plpgsql;

To remove it:

DROP FUNCTION calculate_discount(numeric, numeric);

Notice that I included the parameter types, (numeric, numeric). This is important, and here’s why.

Why You Often Need to Specify Argument Types

PostgreSQL allows function overloading, meaning you can define multiple functions with the same name as long as their parameter signatures differ:

CREATE FUNCTION calculate_discount(price numeric, discount_percent numeric)
RETURNS numeric AS $$ ... $$ LANGUAGE plpgsql;

CREATE FUNCTION calculate_discount(price numeric, discount_percent integer)
RETURNS numeric AS $$ ... $$ LANGUAGE plpgsql;

If you try to drop a function by name alone when multiple overloaded versions exist, PostgreSQL will throw an error because it doesn’t know which one you mean:

ERROR: function name "calculate_discount" is not unique
HINT: Specify the argument list to select the function unambiguously.

That’s why specifying the parameter types is often necessary:

DROP FUNCTION calculate_discount(numeric, integer);

If a function has no overloads (only one version exists with that name), you can often drop it by name alone without specifying argument types, though it’s still good practice to include them for clarity and future-proofing.

Using IF EXISTS

Just like other DROP commands, you can avoid errors when a function might not exist by using IF EXISTS:

DROP FUNCTION IF EXISTS calculate_discount(numeric, numeric);

Without it, trying to drop a nonexistent function throws:

ERROR: function calculate_discount(numeric, numeric) does not exist

Dropping Multiple Functions at Once

You can drop several functions in a single statement:

DROP FUNCTION IF EXISTS calculate_discount(numeric, numeric), calculate_tax(numeric, numeric);

This is handy for cleanup scripts where you’re removing a batch of related, deprecated functions.

Understanding CASCADE and RESTRICT

By default, DROP FUNCTION uses RESTRICT behavior, refusing to drop a function if something depends on it, like a trigger:

DROP FUNCTION update_last_modified_column();

ERROR: cannot drop function update_last_modified_column() because other objects depend on it
DETAIL: trigger set_last_modified on table products depends on function update_last_modified_column()
HINT: Use DROP ... CASCADE to drop the dependent objects too.

If you’re certain you want to remove the function and everything that depends on it, use CASCADE:

DROP FUNCTION update_last_modified_column() CASCADE;

Be careful here, because CASCADE will silently remove the dependent trigger as well. If that trigger is important, you’ll need to recreate both the function and the trigger afterward, or better yet, handle the dependent objects manually instead of blindly using CASCADE.

Checking Function Dependencies Before Dropping

Before running a CASCADE drop, it’s worth checking exactly what depends on a function. You can query the system catalogs:

SELECT dependent_ns.nspname AS dependent_schema,
       dependent_view.relname AS dependent_object
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_proc ON pg_depend.refobjid = pg_proc.oid
WHERE pg_proc.proname = 'update_last_modified_column';

For a simpler check specifically around triggers, you can also query pg_trigger joined with pg_proc to see which triggers call a given function.

Finding the Exact Signature of a Function

If you’re not sure what argument types an existing function has, you can look it up:

SELECT proname, pg_get_function_identity_arguments(oid) AS arguments
FROM pg_proc
WHERE proname = 'calculate_discount';

This gives you the exact argument list you need to reference in your DROP FUNCTION statement.

Alternatively, in psql, you can use:

\df calculate_discount

This lists all functions named calculate_discount, including their argument types and return types, which is very useful when dealing with overloaded functions.

Dropping and Recreating a Function

A very common workflow is dropping a function to redefine it with different logic or a different signature. If you’re only changing the function body (not the parameter types or return type), you can often skip DROP FUNCTION entirely and use CREATE OR REPLACE FUNCTION:

CREATE OR REPLACE FUNCTION calculate_discount(price numeric, discount_percent numeric)
RETURNS numeric AS $$
BEGIN
    RETURN GREATEST(price - (price * discount_percent / 100), 0);
END;
$$ LANGUAGE plpgsql;

However, if you need to change the parameter types, remove a parameter, or change the return type, CREATE OR REPLACE FUNCTION won’t work, and you’ll need to explicitly drop the function first:

DROP FUNCTION IF EXISTS calculate_discount(numeric, numeric);

CREATE FUNCTION calculate_discount(price numeric, discount_percent numeric, minimum_price numeric DEFAULT 0)
RETURNS numeric AS $$
BEGIN
    RETURN GREATEST(price - (price * discount_percent / 100), minimum_price);
END;
$$ LANGUAGE plpgsql;

Permissions Required to Drop a Function

To drop a function, you need to either own the function or be a superuser. If you attempt to drop a function you don’t own:

ERROR: must be owner of function calculate_discount

If ownership needs to change hands, use:

ALTER FUNCTION calculate_discount(numeric, numeric) OWNER TO new_owner_role;

Common Use Cases for DROP FUNCTION

Removing deprecated business logic: cleaning up functions that are no longer part of your application’s workflow.
Refactoring function signatures: dropping an old version before creating a new one with different parameters.
Cleaning up after failed experiments: removing functions created during testing or prototyping that never made it to production use.
Consolidating overloaded functions: simplifying an API by removing redundant overloaded versions of a function.
Security cleanup: removing functions that were created with elevated privileges (like SECURITY DEFINER) and are no longer needed, reducing your attack surface.

Troubleshooting Common Issues

“Function Name Is Not Unique”

This means the function is overloaded. Use \df function_name in psql or query pg_proc to find the exact argument types, then include them in your DROP FUNCTION statement.

“Function Does Not Exist”

Double-check the function name, schema, and argument types. If the function lives in a non-default schema, qualify it:

DROP FUNCTION IF EXISTS reporting.calculate_discount(numeric, numeric);

“Cannot Drop Function Because Other Objects Depend on It”

This usually means a trigger or another function references it. Check dependencies before deciding whether to use CASCADE or handle the dependent objects manually.

Accidentally Dropped a Function Used Elsewhere

Since there’s no built-in undo, keep your function definitions stored in version control or migration files so you can quickly recreate a function if it’s dropped by mistake.

Best Practices for Using DROP FUNCTION

Always specify argument types: even when not strictly required, including the argument list avoids ambiguity, especially in codebases where overloading might be introduced later.
Check dependencies before using CASCADE: understand exactly what will be removed before you commit to a cascading drop.
Use IF EXISTS in migration scripts: this keeps your scripts idempotent and safe to re-run in different environments.
Prefer CREATE OR REPLACE FUNCTION when possible: reserve DROP FUNCTION for cases where the signature or return type actually needs to change.
Keep function definitions in source control: this gives you an easy way to recreate a function if it’s dropped unintentionally, and provides a clear history of how the logic evolved.
Review SECURITY DEFINER functions carefully: since these run with the privileges of the function owner rather than the caller, make sure you understand the security implications before dropping (or keeping) them.

DROP FUNCTION vs DROP PROCEDURE

PostgreSQL distinguishes between functions (created with CREATE FUNCTION) and procedures (created with CREATE PROCEDURE, introduced in PostgreSQL 11). While functions always return a value and can be used inside SELECT statements, procedures are invoked with CALL and can manage their own transactions internally. If you’re trying to remove a procedure, DROP FUNCTION won’t work, you need DROP PROCEDURE instead:

DROP PROCEDURE IF EXISTS process_monthly_billing(integer);

Trying to use DROP FUNCTION on something created with CREATE PROCEDURE will give you an error telling you the object doesn’t exist as a function, since PostgreSQL tracks them as distinct object types even though they can look similar syntactically at a glance.

Handling Functions Used as Default Values

A less obvious dependency scenario involves functions used as default values for table columns:

CREATE TABLE orders (
    id serial PRIMARY KEY,
    order_number text DEFAULT generate_order_number()
);

If you try to drop generate_order_number() while it’s referenced as a column default, PostgreSQL will block you with a dependency error, just like with triggers and views. You’ll need to either alter the column to remove or change the default first, or use CASCADE and be aware that doing so will also strip the default value from the column, though it won’t affect existing data already in the table.

ALTER TABLE orders ALTER COLUMN order_number DROP DEFAULT;
DROP FUNCTION generate_order_number();

Cleaning Up Functions Used by Extensions

If a function was created as part of a PostgreSQL extension (rather than something you wrote yourself), you generally should not drop it directly with DROP FUNCTION. Extension-owned objects are tied to the extension’s lifecycle, and PostgreSQL will typically prevent you from dropping them individually:

ERROR: cannot drop function uuid_generate_v4() because extension "uuid-ossp" requires it
HINT: You can drop extension "uuid-ossp" instead.

In this situation, the correct approach is to drop the entire extension with DROP EXTENSION if you truly don’t need any of its functionality, rather than trying to selectively remove individual functions it provides.

Dropping Functions Safely in a CI/CD Pipeline

If your team uses automated deployment pipelines to apply schema migrations, DROP FUNCTION statements deserve a bit of extra scrutiny compared to more routine changes, precisely because function signature mismatches are a common source of deployment failures. A function drop that works fine in a development database might fail in staging or production simply because a slightly different overload exists there, left over from an earlier, incomplete migration.

A defensive pattern many teams adopt is checking for the function’s existence and exact signature before attempting the drop, rather than assuming the signature matches what’s in the migration script:

DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM pg_proc
        WHERE proname = 'calculate_discount'
        AND pg_get_function_identity_arguments(oid) = 'numeric, numeric'
    ) THEN
        DROP FUNCTION calculate_discount(numeric, numeric);
    END IF;
END $$;

This kind of defensive scripting is more verbose than a plain DROP FUNCTION IF EXISTS, but it protects against the specific case of overload ambiguity that a simple IF EXISTS check doesn’t fully cover, since IF EXISTS still requires the exact signature to be correct.

Reviewing Function Usage Before Cleanup

Before removing a function that’s been in the codebase for a while, it’s worth searching beyond just the database catalog. Functions are often called from application code via an ORM’s raw SQL escape hatch, from scheduled jobs, from other functions, and sometimes from ad hoc scripts that aren’t part of your main codebase at all. A reasonably thorough pre-drop checklist includes:

Searching your application codebase for the function name.
Checking scheduled job definitions (cron jobs, pg_cron entries, external schedulers) for references.
Reviewing any BI tools or reporting dashboards that might call the function directly.
Checking other database functions and triggers for internal calls to it.

Skipping this kind of review is one of the most common reasons a “safe” cleanup turns into an unexpected production incident.

Frequently Asked Questions

Can I drop a function while it’s currently being executed by another session?

PostgreSQL will typically block the DROP FUNCTION statement until any currently running calls to that function complete, since dropping it requires an exclusive lock on the function’s catalog entry. In practice, function calls are usually fast enough that this isn’t noticeable, but for long-running functions, be aware the DROP statement might wait.

What happens to views or queries that reference a dropped function?

Any view, another function, or stored query that calls the dropped function will fail at execution time with an error indicating the function no longer exists, unless you used CASCADE to explicitly drop those dependents along with it.

Is there a way to see all functions in a schema before cleaning up?

Yes, query pg_proc joined with pg_namespace, or use \df schema_name.* in psql to list every function in a given schema along with its arguments and return type.

Does dropping a function release any locks or resources it was holding?

Functions themselves don’t hold persistent locks or resources between calls (any locks taken during execution are released when the calling transaction completes), so there’s no special resource cleanup consideration beyond the normal dependency handling covered above.

Can I drop just one overloaded version of a function and keep the others?

Yes, that’s exactly why specifying argument types matters. DROP FUNCTION calculate_discount(numeric, integer); removes only that specific overload, leaving calculate_discount(numeric, numeric) (if it exists) completely untouched.

Does dropping a function remove any grants that were made on it?

Yes, since the grants are tied directly to the function object’s catalog entry. Once the function is dropped, any GRANT statements that referenced it become meaningless, and if you recreate the function later, you’ll need to re-run the relevant GRANT statements from scratch, since PostgreSQL doesn’t remember previous privilege assignments for an object that no longer exists.

Is there a difference between dropping a function that returns void versus one that returns a value?

No, the DROP FUNCTION syntax and behavior are identical regardless of the function’s return type. The return type only matters for how the function is used in queries, not for how it’s removed from the database.

Wrapping Up

DROP FUNCTION is a simple command in principle, but function overloading and dependency chains can make it trickier than it first appears. Get comfortable checking a function’s exact signature before dropping it, understand what CASCADE will actually remove, and lean on CREATE OR REPLACE FUNCTION whenever you’re just updating logic rather than truly needing to change a function’s shape. With those habits, you’ll be able to manage your PostgreSQL functions confidently as your codebase evolves.