The input parameters of the function follow the name of the function and are separated with commas. Each parameter has an SQL data type specified for it. Arrays cannot be used as function parameters. In addition to the SQL types, the CSTRING type is available for specifying a null-terminated string with a maximum length of LENGTH bytes. There are several mechanisms for passing a parameter from the Firebird engine to an external function, each of these mechanisms will be discussed below.

By default, input parameters are passed by reference. There is no separate clause to explicitly indicate that parameters are passed by reference.

When passing a NULL value by reference, it is converted to the equivalent of zero, for example, a number 0 or an empty string (''). If the keyword NULL is specified after a parameter, then with passing a NULL values, the null pointer will be passed to the external function.

Declaring a function with the NULL keyword does not guarantee that the function will correctly handle a NULL input parameter. Any function must be written or rewritten to correctly handle NULL values. Always use the function declaration as provided by its developer.

If BY DESCRIPTOR is specified, then the input parameter is passed by descriptor. In this case, the UDF parameter will receive a pointer to an internal structure known as a descriptor. The descriptor contains information about the data type, subtype, precision, character set and collation, scale, a pointer to the data itself and some flags, including the NULL indicator. This declaration only works if the external function is written using a handle.

The BY SCALAR_ARRAY clause is used when passing arrays as input parameters. Unlike other types, you cannot return an array from a UDF.

The DECLARE EXTERNAL FUNCTION statement can be executed by:

• Administrators

• Users with the CREATE FUNCTION privilege

The user who created the function becomes its owner.

1. Declaring the addDay external function located in the fbudf module. The input and output parameters are passed by reference.

     |DECLARE EXTERNAL FUNCTION addDay
     |  TIMESTAMP, INT
     |  RETURNS TIMESTAMP
     |  ENTRY_POINT 'addDay' MODULE_NAME 'fbudf';
   

2. Declaring the invl external function located in the fbudf module. The input and output parameters are passed by descriptor.

     |DECLARE EXTERNAL FUNCTION invl
     |  INT BY DESCRIPTOR, INT BY DESCRIPTOR
     |  RETURNS INT BY DESCRIPTOR
     |  ENTRY_POINT 'idNvl' MODULE_NAME 'fbudf';
   

3. Declaring the isLeapYear external function located in the fbudf module. The input parameter is passed by reference, while the output parameter is passed by value.

     |DECLARE EXTERNAL FUNCTION isLeapYear
     |  TIMESTAMP
     |  RETURNS INT BY VALUE
     |  ENTRY_POINT 'isLeapYear' MODULE_NAME 'fbudf';
   

4. Declaring the i64Truncate external function located in the fbudf module. The input and output parameters are passed by descriptor. The second parameter of the function is used as the return value.

     |DECLARE EXTERNAL FUNCTION i64Truncate
     |  NUMERIC(18) BY DESCRIPTOR, NUMERIC(18) BY DESCRIPTOR
     |  RETURNS PARAMETER 2
     |  ENTRY_POINT 'fbtruncate' MODULE_NAME 'fbudf';
   

See alsoSection 5.10.2, “ALTER EXTERNAL FUNCTION”, Section 5.10.3, “DROP EXTERNAL FUNCTION”, CREATE FUNCTION

The ALTER EXTERNAL FUNCTION statement can be executed by:

• Administrators

• Owner of the external function

• Users with the ALTER ANY FUNCTION privilege

Changing the entry point for an external function

  |ALTER EXTERNAL FUNCTION invl ENTRY_POINT 'intNvl';

Changing the module name for an external function

  |ALTER EXTERNAL FUNCTION invl MODULE_NAME 'fbudf2';

See alsoSection 5.10.1, “DECLARE EXTERNAL FUNCTION”, Section 5.10.3, “DROP EXTERNAL FUNCTION”

The DROP EXTERNAL FUNCTION statement can be executed by:

• Administrators

• Owner of the external function

• Users with the DROP ANY FUNCTION privilege

Deleting the declaration of the addDay function.

  |DROP EXTERNAL FUNCTION addDay;

See alsoSection 5.10.1, “DECLARE EXTERNAL FUNCTION”