12.2.1 CREATE FUNCTION/DROP FUNCTION Syntax

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
       SONAME shared_library_name

DROP FUNCTION function_name

A user-definable function (UDF) is a way to extend MySQL with a new function that works like native (built in) MySQL function such as ABS() and CONCAT().

AGGREGATE is a new option for MySQL Version 3.23. An AGGREGATE function works exactly like a native MySQL GROUP function like SUM or COUNT().

CREATE FUNCTION saves the function's name, type, and shared library name in the mysql.func system table. You must have the INSERT and DELETE privileges for the mysql database to create and drop functions.

All active functions are reloaded each time the server starts, unless you start mysqld with the --skip-grant-tables option. In this case, UDF initialisation is skipped and UDFs are unavailable. (An active function is one that has been loaded with CREATE FUNCTION and not removed with DROP FUNCTION.)

For instructions on writing user-definable functions, see section 12.2 Adding New Functions to MySQL. For the UDF mechanism to work, functions must be written in C or C++, your operating system must support dynamic loading and you must have compiled mysqld dynamically (not statically).

Note that to make AGGREGATE work, you must have a mysql.func table that contains the column type. If this is not the case, you should run the script mysql_fix_privilege_tables to get this fixed.

User Comments

Is it possible to use function UDF in CREATE INDEX
declaration ? For example : CREATE INDEX
name_udf ON customer (MY_UDF(name)).
It's avoid the creation of an intermediate column
with the data transformed.

Hello,

can somebody help me? i tried to write a module for mysql. the function works fine (i tested it in a program which i wrote for testing UDFs) but when i load it into mysql and start a query which use this function, i get a message that the connection was lost during the query...

why? can anybody solve my problem?
please mail me: alex@freecoder.de

thanks!!

Add your own comment.