EXTERNAL FUNCTION

Table of Contents

DECLARE EXTERNAL FUNCTION
ALTER EXTERNAL FUNCTION
DROP EXTERNAL FUNCTION

	REVIEW STATUS
	All sections from this point forward to the end of the chapter are awaiting technical and editorial review.

External functions, also known as “user-defined functions” (UDFs) are programs written in an external programming language and stored in dynamically loaded libraries. Once declared to a database, they become available in dynamic and procedural statements as though they were implemented in the SQL language internally.

External functions extend the possibilities for processing data with SQL considerably. To make a function available to a database, it is declared using the statement DECLARE EXTERNAL FUNCTON.

The library containing a function is loaded when any function included in it is called.

	Note
	External functions may be contained in more than one library—or “module”, as it is referred to in the syntax.

DECLARE EXTERNAL FUNCTION

Table of Contents

DECLARE EXTERNAL FUNCTION Input Parameters

Used for: Declaring a user-defined function (UDF) to the database

Available in: DSQL, ESQL

Syntax:

DECLARE EXTERNAL FUNCTION funcname
[<arg_type_decl> [, <arg_type_decl> ...]]
RETURNS {
  sqltype [BY {DESCRIPTOR | VALUE}] |
  CSTRING(length) |
  PARAMETER param_num }
[FREE_IT]
ENTRY_POINT 'entry_point' MODULE_NAME 'library_name';

<arg_type_decl> ::=
  sqltype [{BY DESCRIPTOR} | NULL] |
  CSTRING(length) [NULL]

Table 5.27. DECLARE EXTERNAL FUNCTION Statement Parameters

Parameter	Description
funcname	Function name in the database. It may consist of up to 31 characters. It should be unique among all internal and external function names in the database and need not be the same name as the name exported from the UDF library via ENTRY_POINT.
entry_point	The exported name of the function
library_name	The name of the module (MODULE_NAME from which the function is exported. This will be the name of the file, without the “.dll” or “.so” file extension.
sqltype	SQL data type. It cannot be an array or an array element
length	The maximum length of a null-terminated string, specified in bytes
param_num	The number of the input parameter, numbered from 1 in the list of input parameters in the declaration, describing the data type that will be returned by the function

The DECLARE EXTERNAL FUNCTION statement makes a user-defined function available in the database. UDF declarations must be made in each database that is going to use them. There is no need to declare UDFs that will never be used.

The name of the external function must be unique among all function names. It may be different from the exported name of the function, as specified in the ENTRY_POINT argument.

DECLARE EXTERNAL FUNCTION Input Parameters

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. As well as the SQL types, the CSTRING type is available for specifying a null-terminated string with a maximum length of LENGTH bytes.

By default, input parameters are passed by reference. The BY DESCRIPTOR clause may be specified instead, if the input parameter is passed by descriptor. Passing a parameter by descriptor makes it possible to process NULLs.

Clauses and Keywords

RETURNS clause: (Required) specifies the output parameter returned by the function. A function is scalar: it returns one and only one parameter. The output parameter can be of any SQL type (except an array or an array element) or a null-terminated string (CSTRING). The output parameter can be passed by reference (the default), by descriptor or by value. If the BY DESCRIPTOR clause is specified, the output parameter is passed by descriptor. If the BY VALUE clause is specified, the output parameter is passed by value.

PARAMETER keyword: specifies that the function returns the value from the parameter under number param_num. It is necessary if you need to return a value of data type BLOB.

FREE_IT keyword: means that the memory allocated for storing the return value will be freed after the function is executed. It is used only if the memory was allocated dynamically in the UDF. In such a UDF, the memory must be allocated with the help of the ib_util_malloc function from the ib_util module, a requirement for compatibility with the functions used in Firebird code and in the code of the shipped UDF modules, for allocating and freeing memory.

ENTRY_POINT clause: specifies the name of the entry point (the name of the imported function), as exported from the module.

MODULE_NAME clause: defines the name of the module where the exported function is located. The link to the module should not be the full path and extension of the file, if that can be avoided. If the module is located in the default location (in the ../UDF subdirectory of the Firebird server root) or in a location explicitly configured in firebird.conf, it makes it easier to move the database between different platforms. The UDFAccess parameter in the firebird.conf file allows access restrictions to external functions modules to be configured.

Any user connected to the database can declare an external function (UDF).

Examples using DECLARE EXTERNAL FUNCTION:

Declaring the addDate 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';
          
```

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';

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';
          
```
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';
          
```

ALTER EXTERNAL FUNCTION

Used for: Changing the entry point and/or the module name for a user-defined function (UDF)

Available in: DSQL

Syntax:

ALTER EXTERNAL FUNCTION funcname
[ENTRY_POINT 'new_entry_point']
[MODULE_NAME 'new_library_name'];

Table 5.28. ALTER EXTERNAL FUNCTION Statement Parameters

Parameter	Description
funcname	Function name in the database
new_entry_point	The new exported name of the function
new_library_name	The new name of the module (MODULE_NAME from which the function is exported. This will be the name of the file, without the “.dll” or “.so” file extension.

The ALTER EXTERNAL FUNCTION statement changes the entry point and/or the module name for a user-defined function (UDF). Existing dependencies remain intact after the statement containing the change[s] is executed.

The ENTRY_POINT clause: is for specifying the new entry point (the name of the function as exported from the module).

The MODULE_NAME clause: Is for specifying the new name of the module where the exported function is located.

Any user connected to the database can change the entry point and the module name.

Examples using ALTER EXTERNAL FUNCTION:

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';

DROP EXTERNAL FUNCTION

Used for: Removing a user-defined function (UDF) from a database

Available in: DSQL, ESQL

Syntax:

DROP EXTERNAL FUNCTION funcname

Table 5.29. DROP EXTERNAL FUNCTION Statement Parameter

Parameter	Description
funcname	Function name in the database

The DROP EXTERNAL FUNCTION statement deletes the declaration of a user-defined function from the database. If there are any dependencies on the external function, the statement will fail and the appropriate error will be raised.

Any user connected to the database can delete the declaration of an internal function.

Example using DROP EXTERNAL FUNCTION: Deleting the declaration of the addDay function.

DROP EXTERNAL FUNCTION addDay;