Registering UDFs

 

A user-defined function (UDF) must be registered in the database before the function can be recognized and used by SQL.

The statement allows you to specify the language and name of the program, along with options such as DETERMINISTIC, ALLOW PARALLEL, and RETURNS NULL ON NULL INPUT. These options help to more specifically identify to the database the intention of the function and how calls to the database can be optimized.

You should register an external UDF after you have written and completely tested the actual code. It is possible to define the UDF before actually writing it. However, to avoid any problems with running your UDF, you are encouraged to write and test it extensively before registering it.

• Example: Exponentiation
  Suppose that you write an external function to perform exponentiation of floating point values, and you want to register it in the MATH schema.

• Example: String search
  Suppose that you write a user-defined function (UDF) to look for a given string, passed as an argument, within a given character large object (CLOB) value that is also passed as an argument. The UDF returns the position of the string within the CLOB if it finds the string, or zero if it does not.

• Example: BLOB string search
  Suppose that you want the FINDSTRING function to work on binary large objects (BLOBs) as well as on character large objects (CLOBs). To do this, you define another FINDSTRING function that takes BLOB as the first parameter.

• Example: String search over a user-defined type (UDT)
  Suppose that you are satisfied with the FINDSTRING function from the binary large object (BLOB) string search, but now you want to define a distinct type BOAT with source type BLOB.

• Example: AVG over a user-defined type (UDT)
  This example implements the AVG column function over the CANADIAN_DOLLAR distinct type.

• Example: Counting
  Your simple counting function returns a 1 the first time and increments the result by one each time it is called. This function takes no SQL arguments. By definition, it is a NOT DETERMINISTIC function because its answer varies from call to call.

• Example: Table function returning document IDs
  Suppose that you write a table function that returns a row consisting of a single document identifier column for each known document that matches a given subject area (the first parameter) and contains the given string (second parameter).

 

Parent topic:

Writing UDFs as external functions

 

Related reference

CREATE FUNCTION