FirebirdSQL logo
 FUNCTIONPACKAGE 
Note

External functions (UDFs) have been aggressively deprecated in Firebird 4.0:

  • The default setting for the configuration parameter UdfAccess is None.To use UDFs now requires an explicit configuration of Restrict path-list

  • The UDF libraries (ib_udf, fbudf) are no longer distributed in the installation kits

  • Most of the functions in the libraries previously distributed in the shared (dynamic) libraries ib_udf and fbudf have already been replaced with built-in functions.A few remaining UDFs have been replaced with either compatible routines in a new library of UDRs named udf_compat or converted to stored functions.

    Refer to Deprecation of External Functions (UDFs) in the Compatibility chapter of the Firebird 4.0 Release notes for details and instructions about upgrading to use the safe functions.

  • Replacement of UDFs with UDRs or stored functions is strongly recommended

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 in a database, they become available in dynamic and procedural statements as though they were implemented in the SQL language.

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

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

Note

External functions declared as DECLARE EXTERNAL FUNCTION are a legacy from previous versions of Firebird.Their capabilities are inferior to the capabilities of the new type of external functions, UDR (User-Defined Routine).Such functions are declared as CREATE FUNCTION …​ EXTERNAL …​.See CREATE FUNCTION for details.
Note

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

UDFs are fundamentally insecure.We recommend avoiding their use whenever possible, and disabling UDFs in your database configuration (UdfAccess = None in firebird.conf; this is the default since Firebird 4).If you do need to call native code from your database, use a UDR external engine instead.
See also

FUNCTION

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.