Examples

Example 1: Assume an external function program in C is needed that implements the following logic:

    rslt   = 2 * input - 4

The function should return a null value if and only if one of the input arguments is null. The simplest way to avoid a function call and get a null result when an input value is null is to specify RETURNS NULL ON NULL INPUT on the CREATE FUNCTION statement. The following statement defines the function, using the specific name MINENULL1.

  CREATE FUNCTION NTEST1 (SMALLINT)
      RETURNS SMALLINT
      EXTERNAL NAME NTESTMOD
      SPECIFIC MINENULL1
      LANGUAGE C
      DETERMINISTIC
      NO SQL
      FENCED
      PARAMETER STYLE SQL
      RETURNS NULL ON NULL INPUT
      NO EXTERNAL ACTION

The program code:

  void nudft1
    (int *input,                 /* ptr to input argument         */
     int *output,                /* ptr to output argument        */
     short *input_ind,           /* ptr to input indicator        */
     short *output_ind,          /* ptr to output indicator       */
     char sqlstate[6],  /* sqlstate                      */
     char fname[140],   /* fully qualified function name */
     char finst[129],   /* function specific name        */
     char msgtext[71])  /* msg text buffer               */
  {
    if (*input_ind == -1)
      *output_ind = -1;
    else
  {
      *output = 2*(*input)-4;
      *output_ind = 0;
    }
    return;
 }                                

Example 2: Assume that a user wants to define an external function named CENTER. The function program will be written in C. The following statement defines the function, and lets the database manager generate a specific name for the function. The name of the program containing the function body is the same as the name of the function, so the EXTERNAL clause does not include 'NAME external-program-name'.

   CREATE FUNCTION CENTER (INTEGER, FLOAT)
      RETURNS FLOAT
      LANGUAGE C
      DETERMINISTIC
      NO SQL
      PARAMETER STYLE SQL
      NO EXTERNAL ACTION

Example 3: Assume that user McBride (who has administrative authority) wants to define an external function named CENTER in the SMITH schema. McBride plans to give the function specific name FOCUS98. The function program uses a scratchpad to perform some one-time only initialization and save the results. The function program returns a value with a DOUBLE data type. The following statement written by user McBride defines the function and ensures that when the function is invoked, it returns a value with a data type of DECIMAL(8,4).

   CREATE FUNCTION SMITH.CENTER (DOUBLE, DOUBLE, DOUBLE)
      RETURNS DECIMAL(8,4)
      CAST FROM DOUBLE
      EXTERNAL NAME CMOD
      SPECIFIC FOCUS98
      LANGUAGE C
      DETERMINISTIC
      NO SQL
      FENCED
      PARAMETER STYLE SQL
      NO EXTERNAL ACTION
      SCRATCHPAD
      NO FINAL CALL

Example 4: The following example defines a Java(TM) user-defined function that returns the position of the first vowel in a string. The user-defined function is written in Java, is to be run fenced, and is the FINDVWL method of class JAVAUDFS.

   CREATE FUNCTION FINDV (VARCHAR(32000))
      RETURNS INTEGER
      FENCED
      LANGUAGE JAVA
      PARAMETER STYLE JAVA
      EXTERNAL NAME 'JAVAUDFS.FINDVWL'
      NO EXTERNAL ACTION
      CALLED ON NULL INPUT
      DETERMINISTIC
      NO SQL