In PostgreSQL, the DROP and DROP IF EXISTS statements are used to delete any existing database object. We can drop a database, table, column, function, any extension, etc. in Postgres by using the DROP or DROP IF EXISTS statements. These statements do the same job of dropping an object but they also have a difference in their working. We will specifically talk about the DROP FUNCTION IF EXISTS statement.
The DROP FUNCTION statement drops/deletes a specified function. But what is the function that is specified does not exist already? The DROP FUNCTION statement will definitely throw an error in such a case. To handle such cases, DROP FUNCTION IF EXISTS is used. It drops the specified function only if that function exists and, in this way,, it avoids error.
Let’s see how the DROP FUNCTION IF EXISTS statement works.
How Does the Postgres Drop Function If Exists Work?
The DROP FUNCTION IF EXISTS drops the specified function if it exists in the database. This function will not throw an error if the function is not present in the database. The fundamental and basic syntax for the DROP FUNCTION IF EXISTS statement is given as:
DROP FUNCTION IF EXISTS func_name (args) [Cascade | restrict];
In the above syntax:
● After the DROP FUNCTION IF EXIST statement, we have to write the function’s name that we want to drop.
● We can optionally use the CASCADE and RESTRICT options.
Let’s see how this statement works with the help of an example.
Example: Postgres Drop Function If Exists
Let’s consider the following query for dropping a function through the DROP FUNCTION IF EXISTS statement:
DROP FUNCTION IF EXISTS example_function;
The “example_function” is the user-defined function already existing in my database. The above query is written to drop the function using the DROP FUNCTION IF EXISTS statement. The query will simply drop the function because it is already existing. The output is:
We can see that the function has been dropped.
Now consider the case for dropping a function that does not exist. The query will be:
DROP FUNCTION IF EXISTS non_existing_function;
The above query will not return an error as in the case of the DROP function. The DROP FUNCTION IF EXISTS statement will instead raise a notice and the other query parts will work fine.
The output for the above query is:
If we write another statement with this we can see that if the function does not exist it will not return an error. It will let the other statement execute properly without causing an error. Like this:
We can see that dropping a non-existing function has not caused any error if we used the DROP FUNCTION IF EXISTS statement and has not disturbed the execution of the second statement.
That is how the Postgre only if that function exists and in this way, it avoids error. While the DROP FUNCTION statement also performs the same function, but it throws an error if the function does not exist. In this blog, we have seen the functioning of the PostgreSQL DROP FUNCTION IF EXISTS statement in detail using practical examples.SQL DROP FUNCTION IF EXISTS.
Conclusion
DROP FUNCTION IF EXISTS drops the specified function only if that function exists and in this way, it avoids error. While the DROP FUNCTION statement also performs the same function, it throws an error if the function does not exist. In this blog, we have seen the functioning of the PostgreSQL DROP FUNCTION IF EXISTS statement in detail using practical examples.
