Remove a Procedure or a Function From a Database
When developing tools that interact with various DBMS platforms, subtle differences in syntax and feature support can pose a challenge. One such case is how to drop stored procedure definitions, as SQL support and conventions vary between systems.
This article explores the drop stored procedure syntax and behavior across popular relational database management systems (RDBMS), helping developers understand which statements are supported and how to use them correctly.
Only relational DBMS are considered in the article.
PostgreSQL
It is possible to create 2 types of functions in PostgreSQL: normal and aggregate. Procedures can be created since version 11 released at the end of 2018. Functions, including aggregate functions, are available in early versions.
Using a DROP ROUTINE statement, it is possible to remove any function or procedure. The query syntax:
DROP ROUTINE function_or_procedure_name(argument_types)
where:
- argument_types – argument types separated by a comma.
For example:
DROP ROUTINE my_function(INTEGER, INTEGER)
There might be functions and procedures with the same name but with different arguments. Creating a function with the same name is called a function overloading. To eliminate ambiguity while removing procedures or functions, the arguments need to be specified. If the function or procedure is not overloaded, then it is not necessary to specify argument types while it is removed:
DROP ROUTINE function_or_procedure_name
There are separate statements to remove aggregate functions, normal functions and procedures respectively:
- DROP AGGREGATE
- DROP FUNCTION
- DROP PROCEDURE
The syntax of these statements is the same as DROP ROUTINE’s.
DROP ROUTINE and DROP PROCEDURE statements and procedures came up in the version 11. DROP AGGREGATE and DROP FUNCTION statements are used to remove functions in earlier versions.
ORACLE
The next statement is used to remove procedures in Oracle:
DROP PROCEDURE procedure_name
The statement similar to previous is used to remove functions:
DROP FUNCTION function_name
For example:
DROP PROCEDURE my_procedure;
Functions and procedures can be standalone or can be created in a collection using a package. To remove functions and procedures from the package is possible only via removing or redefining the package.
Standalone functions and procedures cannot be overloaded, so there is no need to specify argument types while removing.
MySQL and MariaDB
The next statement is used to remove procedures in MySQL and MariaDB:
DROP PROCEDURE procedure_name
The statement similar to previous is used to remove functions:
DROP FUNCTION function_name
MySQL and MariaDB don’t support overloading of procedures or functions; therefore, only a name is specified.
Since 10.3.3 version (23rd December 2017) it is possible to create aggregate functions in MariaDB. Aggregate functions are removed as normal ones by using the DROP FUNCTION statement.
SQLite
SQLite doesn’t support creating procedures and functions, so there is no statement to remove them.
MS SQL Server
The next statements are used to remove procedures, normal functions and aggregate functions in MS SQL:
DROP PROCEDURE procedure_name
DROP FUNCTION normal_function_name
DROP AGGREGATE aggregate_function_name
MS SQL doesn’t support overloading of procedures and functions; so, while removing them only the name is specified.
As opposite to procedures and normal functions, aggregate functions in MS SQL only refer to an outside implementation.
Netezza
The next statements are used to remove procedures, normal functions and aggregate functions in Netezza:
DROP PROCEDURE procedure_name(argument_types)
DROP FUNCTION normal_function_name(argument_types)
DROP AGGREGATE aggregate_function_name(argument_types)
where:
- argument_types – argument types separated by a comma.
Netezza supports overloading of functions and procedures; therefore, argument types are necessary. As opposite to procedures, functions in Netezza only refer to an external implementation.
Informix
The next statements are used to remove procedures, normal functions and aggregate functions in Informix:
DROP PROCRDURE procedure_name(argument_types)
DROP FUNCTION normal_function_name(argument_types)
DROP AGGREGATE aggregate_function_name
where:
- argument_types – argument types separated by a comma.
It is not necessary to specify argument types for aggregate functions.
It is possible to overload procedures; therefore, few procedures can have the same name. To distinguish them it is possible to give a procedure a unique name while creating it. That is true for normal functions, too. A unique name can be used for removing procedures and normal functions in the next statements:
DROP SPECIFIC PROCEDURE unique_procedure_name
DROP SPECIFIC FUNCTION unique_normal_function_name
If a procedure or a normal function doesn’t have overloading, then it isn’t necessary to specify argument types when removing it.
DROP PROCEDURE procedure_name
DROP FUNCTION normal_function_name
The next statement provides removing a procedure or a normal function:
DROP ROUTINE name(argument_types)
or in case there are no overloadings:
DROP ROUTINE name
This statement is useful when it is unknown what needs to be removed – a procedure or a function.
Besides, a unique name can be used for removing:
DROP SPECIFIC ROUTINE unique_name
IBM Db2
The next statements are used to remove procedures and functions in IBM Db2:
DROP PROCEDURE procedure_name(argument_types)
DROP PROCEDURES function_name(argument_types)
where:
- argument_types – argument types separated by a comma.
Procedures can be overloaded; therefore, few procedures can have the same name. To define them it is possible to set a unique value while creating a procedure. That is true for functions, too. A unique name can be used to remove procedures and functions via the next statements:
DROP SPECIFIC PROCEDURE unique_procedure_name
DROP SPECIFIC FUNCTION unique_function_name
If a procedure or a function doesn’t have overloadings, then it is not necessary to specify argument types while removing it:
DROP PROCEDURE procedure_name
DROP FUNCTION function_name
AWS Athena
Athena doesn’t support creating procedures and functions; therefore, there is no possibility to remove them.
Teradata
The next statements are used to remove procedures and functions in Teradata:
DROP PROCEDURE procedure_name
DROP FUNCTION function_name(argument_types)
where:
- argument_types – argument types separated by a comma.
Teradata provides to overload a function (but not a procedure). In a CREATE FUNCTION statement in addition to a function name, it is necessary to specify a unique name of the function while overloading. Using a unique name, it is possible to remove a function with the help of the next statement:
DROP SPECIFIC FUNCTION unique_function_name
If a function doesn’t have overloadings, it is possible not to specify argument types when removing:
DROP FUNCTION function_name
Teradata supports macros which somewhat are like procedures. The next statement is used to remove macros:
DROP MACRO macro_name
Vertica
Vertica provides creating new procedures (CREATE PROCEDURE statement), which refer to an external executable file. Stored procedures are not supported.
The next statement is used to remove procedures:
DROP PROCEDURE procedure_name(argument_types)
where:
- argument_types – argument types separated by a comma.
Procedures can be overloaded. Argument types are necessary even if the procedure doesn’t have overloadings.
Vertica supports a lot of function types:
- Aggregate functions (CREATE AGGREGATE)
- Analytical functions (CREATE ANALITIC FUNCTION)
- Load filter functions (CREATE FUNCTION)
- Load parser functions (CREATE PARSER)
- Load source functions (CREATE SOURCE)
- Transformation functions (CREATE TRANSFORM FUNCTION)
- Scalar functions (CREATE FUNCTION)
- SQL-functions (CREATE FUNCTION)
There are statements in brackets which help to create functions.
All functions except SQL-functions have an external implementation, that is, they refer to an outside dynamic library.
SQL-functions are stored in a database but can use simple queries only.
There is a statement to remove functions for each function types, except analytic ones.
DROP AGGREGATE FUNCTION name(argument_types)
DROP FILTER name()
DROP SOURCE name()
DROP PARSER name()
DROP TRANSFORM FUNCTION name(argument_types)
DROP FUNCTION name(argument_types)
where:
- argument_types – argument types separated by a comma.
Any function can be removed by a DROP FUNCTION statement, except aggregate and transform functions. Functions can be overloaded, therefore, to remove them it is necessary to specify argument types. They are needed even if the function doesn’t have overloadings.
SAP HANA
The next statements are used to remove procedures and functions in SAP HANA:
DROP PROCEDURE procedure_name
DROP FUNCTION function_name
Overloading of functions and procedures is not supported; therefore, it is specified only a name when removing.
Apache Impala
Impala allows creating a function (scalar and aggregate) with an external implementation. Scalar functions can be implemented in C++ and Java. Aggregate does C++ only. The next statement is used to remove aggregate functions:
DROP AGGREGATE FUNCTION function_name(argument_types)
where:
- argument_types – argument types separated by a comma.
The statement to remove a scalar function depends on a language implementation.
The next statement is used to remove a scalar function in C++:
DROP FUNCTION scalar_function_name(arguments_types)
The next statement is used to remove a scalar function in Java:
DROP FUNCTION scalar_function_name
Impala allows overloading of functions; therefore, it is necessary to specify argument types when removing. Java functions are overloaded by Java itself, for this reason, arguments don’t need to be specified.
HPL/SQL provides greater opportunities for using procedures and functions.
Apache Hive
Hive allows creating functions with an external implementation in Java. Functions can be temporary or permanent. Temporary functions exist only in the current session.
The next statement is used to remove permanent functions:
DROP FUNCTION function_name
The next statement is used to remove temporary functions:
DROP TEMPORARY FUNCTION function_name
Hive allows creating temporary macros which can contain simple expressions. The next statement is used to remove macros:
DROP TEMPORARY MACRO macro_name
HPL/SQL provides greater opportunities for using procedures and functions.
Different DBMS documentation: