USER_FUNCTIONS

Returns metadata about user-defined SQL functions (which store commonly used SQL expressions as a function in the Vertica catalog) and user-defined functions.

Column Name Data Type Description 
SCHEMA_NAME

VARCHAR

The name of the schema in which this function exists.

 
FUNCTION_NAME

VARCHAR

The name assigned by the user to the SQL function or user-defined function.

 
PROCEDURE_TYPE

VARCHAR

The type of user-defined function. For example, 'User Defined Function'.

 
FUNCTION_RETURN_TYPE

VARCHAR

The data type name that the SQL function returns.

 
FUNCTION_ARGUMENT_TYPE

VARCHAR

The number and data types of parameters for the function.

 
FUNCTION_DEFINITION

VARCHAR

The SQL expression that the user defined in the SQL function's function body.

 
VOLATILITY

VARCHAR

The SQL function's volatility (whether a function returns the same output given the same input). Can be immutable, volatile, or stable.

 
IS_STRICT

BOOLEAN

Indicates whether the SQL function is strict, where t is true and f is false.

 
IS_FENCED

BOOLEAN

Indicates whether the function runs in Fenced and Unfenced Modes or not.

 
COMMENT

VARCHAR

A comment about this function provided by the function creator.

Notes

  • The volatility and strictness of a SQL function are automatically inferred from the function definition in order that Vertica determine the correctness of usage, such as where an immutable function is expected but a volatile function is provided.
  • The volatility and strictness of a UDx is defined by the UDx's developer.

Example

Create a SQL function called myzeroifnull in the public schema:

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

Now query the USER_FUNCTIONS table. The query returns just the myzeroifnull macro because it is the only one created in this schema:

=> SELECT * FROM user_functions;
-[ RECORD 1 ]----------+---------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
procedure_type         | User Defined Function
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
is_fenced              | f
comment                |

See Also