FirebirdSQL logo

User-Defined Functions (UDFs)

UDFs (User-Defined Functions) are functions that are not internal to the engine, but defined in separate modules.Firebird ships with two UDF libraries: ib_udf (a widely used “InterBase library”) and fbudf.You can add more libraries, e.g. by buying or downloading them, or by writing them yourself.UDFs can’t be used out of the box;they have to be “declared” to the database first.This also applies to the UDFs that come with Firebird itself.

NULL <=> non-NULL conversions you didn’t ask for

Teaching you how to declare, use, and write UDFs is outside the scope of this guide.However, we must warn you that UDFs can occasionally perform unexpected NULL conversions.This will sometimes result in NULL input being converted to a regular value, and other times in the nullification of valid input like '' (an empty string).

The main cause of this problem is that with “old style” UDF calling (inherited from InterBase), it is not possible to pass NULL as input to the function.When a UDF like LTRIM (left trim) is called with a NULL argument, the argument is passed to the function as an empty string.(Note: in Firebird 2 and up, it can also be passed as a null pointer.We’ll get to that later.)From inside the function there is no way of telling if this argument represents a real empty string or a NULL.So what does the function implementor do?He has to make a choice: either take the argument at face value, or assume it was originally a NULL and treat it accordingly.

If the function result type is a pointer, returning NULL is possible even if receiving NULL isn’t.Thus, the following unexpected things can happen:

  • You call a UDF with a NULL argument.It is passed as a value, e.g. 0 or ''.Within the function, this argument is not changed back to NULL;a non-NULL result is returned.

  • You call a UDF with a valid argument like 0 or ''.It is passed as-is (obviously).But the function code supposes that this value really represents a NULL, treats it as a black hole, and returns NULL to the caller.

Both conversions are usually unwanted, but the second probably more so than the first (better validate something NULL than wreck something valid).To get back to our LTRIM example: in Firebird 1.0, this function returns NULL if you feed it an empty string.This is wrong.In 1.5 it never returns NULL: even NULL strings (passed by the engine as '') are “trimmed” to empty strings.This is also wrong, but it’s considered the lesser of two evils.Firebird 2 has finally got it right: a NULL string gives a NULL result, an empty string is trimmed to an empty string — at least if you declare the function in the right way.

Descriptors

As early as in Firebird 1.0, a new method of passing UDF arguments and results was introduced: “by descriptor”.Descriptors allow NULL signalling no matter the type of data.The fbudf library makes ample use of this technique.Unfortunately, using descriptors is rather cumbersome;it’s more work and less play for the UDF implementor.But they do solve all the traditional NULL problems, and for the caller they’re just as easy to use as old-style UDFs.

Improvements in Firebird 2

Firebird 2 comes with a somewhat improved calling mechanism for old-style UDFs.The engine will now pass NULL input as a null pointer to the function, if the function has been declared to the database with a NULL keyword after the argument(s) in question, e.g. like this:

declare external function ltrim
  cstring(255) null
  returns cstring(255) free_it
  entry_point 'IB_UDF_ltrim' module_name 'ib_udf';

This requirement ensures that existing databases and their applications can continue to function like before.Leave out the NULL keyword and the function will behave like it did under Firebird 1.5 and earlier.

Please note that you can’t just add NULL keywords to your declarations and then expect every function to handle NULL input correctly.Each function has to be (re)written in such a way that NULLs are dealt with correctly.Always look at the declarations provided by the function implementor.For the functions in the ib_udf library, consult ib_udf2.sql in the Firebird UDF directory.Notice the 2 in the file name;the old-style declarations are in ib_udf.sql.

These are the ib_udf functions that have been updated to recognise NULL input and handle it properly:

  • ascii_char

  • lower

  • lpad and rpad

  • ltrim and rtrim

  • substr and substrlen

Most ib_udf functions remain as they were;in any case, passing NULL to an old-style UDF is never possible if the argument isn’t of a referenced type.

On a side note: don’t use lower, *trim and substr* in new code;use the internal functions LOWER, TRIM and SUBSTRING instead.

“Upgrading” ib_udf functions in an existing database

If you are using an existing database with one or more of the functions listed above under Firebird 2, and you want to benefit from the improved NULL handling, run the script ib_udf_upgrade.sql against your database.It is located in the Firebird misc\upgrade\ib_udf directory.