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:
\df
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:
DROP FUNCTION IF EXISTS function_name;
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:
DROP FUNCTION IF EXISTS function_name RESTRICT;
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:
DROP FUNCTION IF EXISTS function_name CASCADE;
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!
