Altering and Dropping User-Defined SQL Functions
Vertica allows multiple functions to share the same name with different argument types. Therefore, if you try to alter or drop a SQL function without specifying the argument data type, the system returns an error message to prevent you from dropping the wrong function:
=> DROP FUNCTION myzeroifnull(); ROLLBACK: Function with specified name and parameters does not exist: myzeroifnull
Note: Only a superuser or owner can alter or drop a SQL Function.
Altering a User-Defined SQL Function
The ALTER FUNCTION (UDF or UDT) command lets you assign a new name to a user-defined function, as well as move it to a different schema.
In the previous topic, you created a SQL function called myzeroifnull
. The following command renames the myzeroifnull
function to zerowhennull
:
=> ALTER FUNCTION myzeroifnull(x INT) RENAME TO
zerowhennull;
ALTER FUNCTION
This next command moves the renamed function into a new schema called macros
:
=> ALTER FUNCTION zerowhennull(x INT) SET SCHEMA
macros;
ALTER FUNCTION
Dropping a SQL Function
The DROP FUNCTION command drops a SQL function from the Vertica catalog.
Like with ALTER FUNCTION, you must specify the argument data type or the system returns the following error message:
=> DROP FUNCTION zerowhennull(); ROLLBACK: Function with specified name and parameters does not exist: zerowhennull
Specify the argument type:
=> DROP FUNCTION macros.zerowhennull(x INT
);
DROP FUNCTION
Vertica does not check for dependencies, so if you drop a SQL function where other objects reference it (such as views or other SQL Functions), Vertica returns an error when those objects are used, not when the function is dropped.
Tip: To view a list of all user-defined SQL functions on which you have EXECUTE privileges, (which also returns their argument types), query the V_CATALOG.USER_FUNCTIONS system table.