Home » Programming » Databases » How to Drop/Delete a Stored Function in PostgreSQL

How to Drop/Delete a Stored Function in PostgreSQL

This article will show you how to drop/delete a stored function from a PostgreSQL database.

In PostgreSQL, a stored function is a user-definable set of database queries that can be called by name, allowing for easy code re-use. Unlike stored procedures, stored functions require a return value, so they are usually used for calculating values for display, storage, or for further use.

Before you Delete a Function from your Database…

There are a few things you should do before you remove a stored function from your PostgreSQL database, especially if you’re working with data in production.

First, take a backup of your PostgreSQL server – just in case you delete the wrong thing, or change your mind later.

Next, make sure you’re logged in as the default postgresql admin user, or a user with permission to delete the table in question.

And finally, make sure that no queries you run, either from inside or outside your database, call the function you want to delete, or you will start getting errors and suffer potential downtime in your apps. Deleting cannot be undone (unless you have a backup to restore).

Listing Stored Functions

Before you can delete a stored function, you need to know its name. You can list all of the stored functions in your PostgreSQL database by running:


After connecting to your database using psql.

Deleting/Dropping a Stored Function with DROP FUNCTION

In PostgreSQL, the DROP FUNCTION statement is to delete a function:

DROP FUNCTION function_name(arguments);

It is best practice to provide the arguments for the function you are dropping when deleting it, in case there is a function with the same name, but with different arguments. If you only have one function with the given name, it is safe to run:

DROP FUNCTION function_name;

To confirm that the function has been successfully deleted, execute the /df command again and ensure that it no longer appears in the results.

Dropping a Stored Function Only If It Exists

If you attempt to delete a stored function that doesn’t exist, you will receive an error. To prevent this, check whether the function exists before deleting it using the IF EXISTS statement:


Safely Deleting a Stored Function with RESTRICT

Deleting a function that has other objects in the PostgreSQL database that rely on it is dangerous. To safely delete a function only if there are no objects that depend on it, add the RESTRICT statement:


Deleting a Function and All Objects that Depend on It with CASCADE

If you want to delete the stored function and all objects that depend on it, you can use the CASCADE statement:


Be careful! Make sure you know what other PostgreSQL objects reference your function before you delete it with the CASCADE statement, otherwise you may unintentionally delete something you didn’t intend to!

Photo of author
I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Comment