FirebirdSQL logo
 FUNCTIONPACKAGE 

DECLARE EXTERNAL FUNCTION

Declares a user-defined function (UDF) in the current database

Available in

DSQL, ESQL

Syntax
DECLARE EXTERNAL FUNCTION funcname
  [{ <arg_desc_list> | ( <arg_desc_list> ) }]
  RETURNS { <return_value> | ( <return_value> ) }
  ENTRY_POINT 'entry_point' MODULE_NAME 'library_name'

<arg_desc_list> ::=
  <arg_type_decl> [, <arg_type_decl> ...]

<arg_type_decl> ::=
  <udf_data_type> [BY {DESCRIPTOR | SCALAR_ARRAY} | NULL]

<udf_data_type> ::=
    <scalar_datatype>
  | BLOB
  | CSTRING(length) [ CHARACTER SET charset ]

<scalar_datatype> ::=
  !! See Scalar Data Types Syntax !!

<return_value> ::=
  { <udf_data_type> | PARAMETER param_num }
  [{ BY VALUE | BY DESCRIPTOR [FREE_IT] | FREE_IT }]
Table 1. DECLARE EXTERNAL FUNCTION Statement Parameters
Parameter Description

funcname

Function name in the database.The maximum length is 63 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.

length

The maximum length of a null-terminated string, specified in bytes

charset

Character set of the CSTRING

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.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.

Note

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.

Warning

When passing a function parameter by descriptor, the passed value is not cast to the declared data type.

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