Clauses And Keywords

FUNCTIONPACKAGE

Clauses and Keywords

RETURNS clause: (Required) specifies the output parameter returned by the function.A function is scalar, it returns one value (output 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).

Who Can Create an External Function

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.

Examples using `DECLARE EXTERNAL FUNCTION`

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

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

Clauses And Keywords

Clauses and Keywords

Who Can Create an External Function

Examples using DECLARE EXTERNAL FUNCTION

Examples using `DECLARE EXTERNAL FUNCTION`