Subject Re: [firebird-support] how can I Install/Use UDF ?
Author Helen Borrie

At 05:43 PM 30/07/2003 +0200, you wrote:

>But sometimes I can not use more functionality in FireBird. I need in my
>project use functions SUBSTR, LEFT, and more functions what I found in Your
>"Firebird_v1_ReleaseNotes.pdf" documentation part "External Functions
>(UDFs)".. How can I Install and use thouse UDF functions if I install
>FireBird (Firebird- and

UDFs are installed to the *database*, not the server. To make a UDF available to the database, you (1) DECLARE it with database definition language (DDL) statement and (2) make sure the library is located in the /UDF directory (or some other properly configured place).

In the Firebird installation, you will find scripts containing all of the declarations for the UDFs in the two standard UDF libraries (ib_udf.sql and fbudf.sql). Look in the /UDF directory. (In some kits, you might find one or both in the /examples directory). If you can't find them in your kit, you can download them from the CVS tree. (

Just open the script file using a text editor and copy the declaration you want. Paste it into a new script that you maintain for the purpose. Repeat until you have all the declarations you want. In your own script you can include declarations for functions from different libraries.

Run the script and commit it. Now the functions are available to you in SQL and procedures, just as if they were built-in.

Here is a typical declaration from the ib_udf.sql script:



Now, to use it, look at the arguments: in this case, there are three. As the doc tells you, the first is the string expression (which would normally be the identifier of a VARCHAR or CHAR column, but can be any string expression), the second is a number, the 1-based start position, and the third is the end position (up to a limit of 32K).

Now this will work:

select id, substr(description, 1, 10) as descr
from atable

Tip: read the documentation for these functions. They may be implemented to work in a slightly different way from similarly-named functions in other languages.