User Defined Functions in MySQL

Have you experienced a problem that can’t be solved by using MySQL basic functionality, or maybe some function didn’t fit for functionality you needed? Perhaps you have a custom C Program or custom own made library that you want to access from within MySQL database? User Defined Functions is the answer for that problem. MySQL offers you a way to extend basic functionality and create your own functions. Unlike stored procedures or built in functions that come included with MySQL, User Defined Functions is very flexible. MySQL provide an API  for writing User Defined Functions (UDF). User Defined Functions are written in C or C++ Programming Language. In terms of functionality, UDF can do whatever the user needs them to do. limited only by imagination.

User Defined Functions can be particularly useful in combination with a C Client Library such as libgearman to extend more of memcached functionality, make it possible to store and retrieve values even provides for interacting with the memcached server. Basically, if you have a well defined client API with some MySQL standard functions, Writing an User Defined Functions gives you the ability to call the client functions within MySQL.

If you want to learn from example, there are numerous open source UDF are available on the Internet. One usefull site to get UDF example is mysqludf. The first thing that you need to develop a UDF is to know what you want to do with it, what function do you need to access to from within MySQL.

If you have experience on write a C or C++ Programs, you can write a UDF. You should have been familiar with UDF API. An alternative way is to learn from UDF examples. You can get UDF examples in MySQL source code that implements five different functions within the directory and file sql/udf_example.c.

What You Need To Develop User Defined Functions (UDF)

These are the things you should know about writing UDF

• You need a compiler to build your UDF. Most unix variants include it by default.
• It must be run using OS that supports dynamic loading of libraries.
• Consider to use debugger, this can help you during the debugging process.
• UDF must be written in C or C++ Programming Language.
• Functions must have return and accept a string, integer, or real values.
• All functions have to be  thread safe.
• You can have MySQL arguments to a specific type
• Use simple, single row, or multiple row aggregate UDF
• UDF can return three types of values (STRING_RESULT, INT_RESULT, REAL_RESULT)

To create UDF, some standard, basic C functions must be implemented too. These functions is needed for the sake of conversation. For example, we will use UDF_functions().  This functions is mandatory to connect UDF with MySQL. There are three basic functions can be implemented, They are

1. UDF_functions(), this is main function
2. UDF_functions_init(), This is first function called (setup function)
3. UDF_functions_deinit(),  This Function is a cleanup function