Viewing Information About User-Defined SQL Functions
You can access information about any User-Defined SQL Functions on which you have EXECUTE privileges. This information is available in the system table V_CATALOG.USER_FUNCTIONS and from the vsql meta-command \df
.
To view all of the User-Defined SQL Functions on which you have EXECUTE privileges, query the USER_FUNCTIONS table:
=> 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 a User-Defined SQL Function's body, 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 the USER_FUNCTIONS table, 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.
Record 1 still holds the original definition for the myzeroifnull
function:
=> 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