Thursday, January 15, 2015

Creating User Defined Function with MySQL


Referring to the documentation, you may find how you can compile the udf-example.cc from the MySQL source distribution.

http://dev.mysql.com/doc/refman/5.6/en/udf-compiling.html

The article provides the steps for the compilation without downloading the full source distribution but creating your own external functions

We take the example from the UDF source "udf_example.c" downloaded from the following URL
https://github.com/twitter/mysql/blob/master/sql/udf_example.c

Pre-requiste
1. MySQL installation (e.g. /usr/local/mysql)
2. gcc (and gcc-c++) is installed.

The cflag to be used can be obtained by
mysql_config --cflags


To compile the source obtained (such as the one obtained from the above URL) -

 
The shared library is created 'udf_example.so'.   

Copy the shared library into the plugin directory of the mysql folder.
cp udf_example.so /usr/local/mysql/lib/plugin
 


By installing the external function as stated in this udf_example.c, we have the external functions installed.

CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
CREATE FUNCTION sequence RETURNS INTEGER SONAME "udf_example.so";
CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so";
CREATE FUNCTION myfunc_argument_name RETURNS STRING SONAME "udf_example.so";





Testing the functions  (lookup - which the ip address lookup from the hostname)






It is done.  Feel free to try on your own.


No comments:

Post a Comment