Viewing information about user-defined SQL functions

You can access information about user-defined SQL functions on which you have EXECUTE privileges. This information is available in system table USER_FUNCTIONS and from the vsql meta-command \df.

To view all user-defined SQL functions on which you have EXECUTE privileges, query USER_FUNCTIONS:

=> SELECT * FROM USER_FUNCTIONS;
-[ RECORD 1 ]----------+---------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
function_return_type   | Integer
function_argument_type | x Integer
function_definition    | RETURN CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END
volatility             | immutable
is_strict              | f

If you want to change the body of a user-defined SQL function, use the CREATE OR REPLACE syntax. The following command modifies the CASE expression:

=> CREATE OR REPLACE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN
     RETURN (CASE WHEN (x IS NULL) THEN 0 ELSE x END);
   END;

Now when you query USER_FUNCTIONS, you can see the changes in the function_definition column:

=> SELECT * FROM USER_FUNCTIONS;
-[ RECORD 1 ]----------+---------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
function_return_type   | Integer
function_argument_type | x Integer
function_definition    | RETURN CASE WHEN (x IS NULL) THEN 0 ELSE x END
volatility             | immutable
is_strict              | f

If you use CREATE OR REPLACE syntax to change only the argument name or argument type (or both), the system maintains both versions of the function. For example, the following command tells the function to accept and return a numeric data type instead of an integer for the myzeroifnull function:

=> CREATE OR REPLACE FUNCTION myzeroifnull(z NUMERIC) RETURN NUMERIC
   AS BEGIN
     RETURN (CASE WHEN (z IS NULL) THEN 0 ELSE z END);
   END;

Now query the USER_FUNCTIONS table, and you can see the second instance of myzeroifnull in Record 2, as well as the changes in the function_return_type, function_argument_type, and function_definition columns.

=> SELECT * FROM USER_FUNCTIONS;
-[ RECORD 1 ]----------+------------------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
function_return_type   | Integer
function_argument_type | x Integer
function_definition    | RETURN CASE WHEN (x IS NULL) THEN 0 ELSE x END
volatility             | immutable
is_strict              | f
-[ RECORD 2 ]----------+------------------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
function_return_type   | Numeric
function_argument_type | z Numeric
function_definition    | RETURN (CASE WHEN (z IS NULL) THEN (0) ELSE z END)::numeric
volatility             | immutable
is_strict              | f

Because Vertica allows functions to share the same name with different argument types, you must specify the argument type when you alter or drop a function. If you do not, the system returns an error message:

=> DROP FUNCTION myzeroifnull();
ROLLBACK:  Function with specified name and parameters does not exist: myzeroifnull