UDF Example: my_byte_length Declaration

my_byte_length declaration

When my_byte_length resides within the dynamically linkable library my_shared_lib, the declaration for this example is:

CREATE FUNCTION my_byte_length(IN arg1 LONG BINARY) 
//   RETURNS UNSIGNED INT
//   DETERMINISTIC
//   IGNORE NULL VALUES
//   EXTERNAL NAME 'my_byte_length@libudfex'

This declaration says that my_byte_length is a simple scalar UDF residing in my_shared_lib with a descriptor routine named describe_my_byte_length. Since the behavior of a UDF may require more than one actual C/C++ entry point for its implementation, this set of entry points is not directly part of the CREATE FUNCTION syntax. Instead, the CREATE FUNCTION statement EXTERNAL NAME clause identifies a descriptor function for this UDF. A descriptor function, when invoked, returns a descriptor structure. That descriptor structure contains the required and optional function pointers that embody the implementation of this UDF.

This declaration also says that my_byte_length accepts one LONG BINARY argument and returns an UNSIGNED INT result value.

The declaration states that this function is deterministic, which always returns the identical result value when supplied the same input values. This means the result cannot depend on any external information beyond the supplied argument values, or on any side effects from previous invocations. By default, functions are assumed to be deterministic, so the results are the same if this characteristic is omitted from the CREATE statement.

The last piece of this declaration is the IGNORE NULL VALUES characteristic. Nearly all built-in scalar functions return a NULL result value if any of the input arguments are NULL. The IGNORE NULL VALUES states that the my_byte_length function follows that convention, and therefore this UDF routine is not actually invoked when either of its input values is NULL. Since RESPECT NULL VALUES is the default for functions, this characteristic must be specified in the declaration for this UDF to get the performance benefits. All functions that may return a non-NULL result given a NULL input value must use the default RESPECT NULL VALUES characteristic.

This example query with my_byte_length in the SELECT list returns a column with one row for each row in exTable, with an INT representing the size of the binary file:

SELECT my_byte_length(exLOBColumn) 
FROM exTable