USER_FUNCTIONS

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

Returns metadata about user-defined SQL functions (which store commonly used SQL expressions as a function in the OpenText™ Analytics Database 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 the database 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.

Examples

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