Data type declarations for input arguments and return values
The data type declarations for the input arguments and return value of a UDX fall into one of three categories: fixed size, any size, and variable.
- Fixed-size
-
A fixed-size data type declaration is useful when an input argument or return value has a known maximum size. However, because it uses the maximum size for all data points regardless of their actual size, it can result in wasted storage resources when data size varies greatly. The customerName example uses fixed-size data type declarations for its input arguments and return value.
- Any-size
- For CHAR and VARCHAR data types, you can specify the ANY keyword to indicate that the
corresponding value can be of any size. This reduces storage overhead, eliminates the need for
padding blanks, and eliminates the need to register and maintain similar procedures that take
different input arguments or have different return values. For example, the following UDX accepts a
VARCHAR input argument of any length and returns a 4-byte
integer:
CREATE FUNCTION CustomerName(varchar(ANY)) RETURNS integer ...A string specified for an any-size data type can have up to 32K characters (the maximum for a VARCHAR). The UDX must be able to process any valid string.
- Variable
- A variable data type declaration offers even more flexibility than an any-size declaration. With
a variable argument declaration, you specify only the VARARGS keyword in the argument type list, for
example:
CREATE FUNCTION greatest_value(VARARGS) RETURNS INTEGER...A UDX that uses the VARARGS keyword can accept up to 64 input arguments. The UDX code must process the input values and manage them as needed. For example, the function body must verify the data types of the input arguments and either cast them or issue an error message.
You can use variable argument signatures to create a single UDX that can be used for several combinations of input types rather than creating several identically named UDXs that all do the same task for different types or numbers of arguments.
| UDX Type | Allowed Types for Input Argument | Allowed Types for Return Value | AllowedT ypes for State | Notes |
|---|---|---|---|---|
| UDSF | Fixed-size, Any-size, Variable | Fixed-size, Any-size | (does not apply) | |
| UDTF | Fixed-size, Any-size, Variable | Fixed-size, Variable | (does not apply) | A UDTF can use shapers, which are similar in function to generic return values. A UDTF can use a generic table to return variable output. |
| UDAF | Fixed-size, Any-size, Variable | Fixed-size | Fixed-size | A large string state variable can negatively affect performance. |