In this section you will read how to define the function Interface, Create function syntax , Drop function Syntax .

MySQL User Interface

In this section you will read how to define the function Interface, Create function syntax , Drop function Syntax .

MySQL adding new user defined function Two ways are available to add new function to MySQL :

• You can add function with the help of UDF (user defined function) interface. These are complied as object files. After that they can beadded to and removed from the server dynamically by using create and Drop function statements.
   


• You can also add as function as native. In mysqld server these native functions can be compiled and they can be available on a permanent basis.

 There are some advantage or disadvantage of User-defined function and Native function:-

• For user-defined functions, you have to install object files in addition to the server itself. If your function is complied into the server you need not to do that .


•  But in Native function you have to modify a source distribution. But user-defined function does not require to modify a source distribution . But User-defined function can be add to a MySQL binary distribution.


• After upgrading the MySQL distribution, you can continue to use previously installed user-defined function, but if in upgrade version UDF interface are changed then you can not. But for native function, you must repeat you modification each and every time you upgrade.

  MySQL user defined function Interface  MySQL user-defined function  interface provided the following feature:-

• The MySQL User-defined function can return string, integer, or real values.


• User defined function can operate on a single row at a time or aggregate function which operate on groups of rows.


• You can tell MySQL to force arguments to a given type before passing them to a function.


• User defined functions check the number and types of the arguments passed to them.

MySQL create function Syntax The user-defined function is a way to extend mysql with new function. It is work like a native mysql function as ABS( ) or CONCAT( ).
Example :

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

The "function_name" means this function is identified by this name. And by this name SQL statements can invoke the function  The RETURNS clause is used to define the function return value. Decimal function return the string value and write should be string value. The function "shared_library_name" it is a base name of the shared object file. It contains the code that is implement by the function. For creating a function, you must have the INSERT and privilege for the mysql database because in mysql.func system table a new row is added by the CREATE FUNTION that records the name of function, function type and shared library name. But if this table is not available then run the mysql_fix_privilege_tables script to create it.
MySQL AGGREGATE function works like a native function as SUM( ) or COUNT( ). But your mysql.func table have to contain type column, if it is not available then run the mysql_fix_privilege_tables script to create it.

MySQL DROP Function SyntaxBy this statement you can drop the user-defined function named function_name.

DROP FUNCTION function_name

For using this statement you must have the DELETED privilege for the MySQL database. Because this function remove a row from the "mysql.func" system table that record is the function name, function type, and share library name.

MySQL Adding new user defined function There are different type of new user-defined functions are given below :-

• UDF Calling Sequences for simple functionMySQL user-defined function calling describes the order in which MySQL calls these function. This function return type parameter depend on declare the SQL function xxx( ) to be return string, integer, or real is the create function statement.
  
  Example for string function
   
  
  

  char *xxx(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);

  Example of Integer function
  
  
  

  long long xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

  Example of real function
  
  
  

  double xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

The initid parameter points to a UDF_INIT structure, which used to communicate information between functions.

• UDF Calling Sequence for Aggregate function
  This section describes the order in which MySQL calls these function. This function call the different type of functions are given below :
    


• xxx_reset ( )
  It is called when the MySQL finds the first row in a new group. This function should reset any internal summary variables and after that use the functions argument UDF_ARGS as the first value in your internal summary value for the group.
  Example :
   





char *xxx_reset(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

Before MySQL 4.1.1 version xxx_reset() function is used but after MySQL 4.1.1User-defined function interface uses xxx_clear( ) instead of xxx_reset().

• xxx_clear( )The xxx_clear( ) function is called to reset the summary result. And this function is called at the beginning for every new group. Here are the some example of xxx_clear( ) function:
  Example:
    
  
  

  char *xxx_clear(UDF_INIT *initid, char *is_null, char *error);

Here "Is_null" is set to point to char(0) before the calling xxx_clear( ) function. If something happen wrong then the value can be stored in the variable to which the error argument points. and it points to a single byte variable not a string buffer.

• xxx_add( )The xxx_add( )function is used to call for the all row, which belongs to same group but not the first row. The function can be used to add the value in UDF_ARGS argument to your internal variable. Here are the some example of xxx_add( ) function.
  Example:
   
  
  

  char *xxx_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

The user-defined function use the pointer argument to is_null and error are the same call function for the xxx_reset( ), xxx_clear( ) and xxx_add( ) . 

• UDF Argument processing The user-defined function argument is a parameter points to a function UDF_ARGS structure. There are some topic is follow here:-
    


• unsigned int arg_count ( )This unsigned int_count( ) function is check the number of value in the initialization function if you need the function to be called with a particular number of argument.  Here are the some example of unsigned int arg_count ( ) function.
  Example:
   
  
  

  if (args->arg_count != 3) { strcpy(message,"XXX() requires three arguments"); return 1; }

• enum Item_result *arg_type The enum Item_result *arg_type function is a pointer array have the types for each argument. There are some type values are possible like STRING_RESULT, INT_RESULT, REAL_RESULT and DECUMAL_RESULT. You have to confirm about the arguments are the same of given type but if not it return an error. Here are the some example of the enum Item_result *arg_type function.
  Example:
    
  
  

  if (args->arg_type[0] != string_result || args->arg_type[1] != int_result) { strcpy(message,"XXX( ) requires a string and an integer"); return 1; }

• UDF Return values and Error Handling
  If the error occurred then the initialization function should return 0 and else it return 1. At the situation of error, xxx_init() should store a null terminated error message in message argument. This message returns to the client. The message buffer length is equal to MYSQL_ERRMSG_SIZE characters, but you have to try to keep the message length less than 80 characters. The main function xxx() returns the function value, for long long and double functions. The string function should return a pointer to the result set *result and *length . Example:
    
  
  

  memcpy(result, "result string", 13); *length = 13;

The xxx() have the result buffer and its length is 255 bytes. If your result fit in the buffer then you don?t have to allocate more memory for results.
But if your string length is more than 255 bytes, then you have to allocate the space for it by malloc() in your xxx_init() or xxx() function and free it in your xxx_deinit() function. The allocated memory can be stored in the ptr slot in the UDF_INIT structure for reuse by future xxx() calls.

• Compiling and Installing user-defined functions
  User-defined functions must be compiled and installed on the host where the server is running. The sql/udf_example.cc it is included in the MySQL source distribution. Some function are containing by udf_example.cc file are as follows :
   


• Metaphon( ):  The metaphon( ) function return a metaphon string of the string argument.
   


• myfunc_double( ):  myfunc_double( ) function is return the sum of the ASCII values of the characters in its argument and divided by the sum of the length of its argument.
   


• myfunc_int( ): The myfunc_int( ) function returns the sum length of its argument.
   


• sequence([const int]) : The sequence ([const int]) function is returns a sequence starting from the given number or 1 if no number has given
    


• lookup( ): The lookup() function returns the IP number for a hostname.
   


• reverce_lookup( ): The reverce_lookup( ) function return the hostname for IP number.
   


• User-defined function Security Precautions.
  By the INSERT privilege you are able to use CREATE FUNCTION and by the DELETE you are able to use the DROP FUNCTION. By these function you can add and delete rows from the mysql.func table. User-defined function can be have at least one symbol and that can be defined in addition to the xxx symbol, which is corresponds to the main xxx( ) function. These auxiliary symbol corresponds to the xxx_init( ), xxx_deinit( ), xxx_reset( ), xxx_clear( ), and xxx_add( ) function.
  You can not placed the User-define function object files in arbitrary directories. They have to be located in some system directory that the dynamic linker is configured to search.
   


• Adding the new native function
  In this section we are describing you a procedure for adding a new native function. But the native function can not be add to binary distribution becuase the procedure involves modifying the MySQL source code. For adding a new native MySQL function you have to follow these steps :
  
  
  1. In lex.h you have to add one line, which defines the function name in the sql_function[] array.
  
  
  2. If prototype of the function is simple (it takes zero, one, two or three arguments), in lex.h specify SYM(FUNC_ARGN) (N is the number of arguments) as the second argument in the sql_functions[] array and add a function which creates a function object in item_create.cc.
     But if the prototype of the function is complicated(it takes a variable number of arguments) add two lines to sql_yacc.yy. One points the preprocessor symbol that yacc should define (added at the beginning of the file). After that define the function parameters and add an ?item? with these parameters to the simple_expr parsing rule.
  
  
  3. In item_func.h, you have to declare a class that extends the Item_num_func or Item_str_func but it depends on your functions means its returning a number or a string.
  
  
  
  4. In item_func.cc you have to add one the following declaration but its depend on your function means the function is numeric function or string function   
     Example:
       
  
  
  
  

  double Item_func_newname::val() longlong Item_func_newname::val_int() String *Item_func_newname::Str(String *str)

    
  
  5. You should also define the following object function.
      
     
     

     void Item_func_newname::fix_length_and_dec()

  
  
  It is used to calculate max_length that is based on the given arguments. Max_length has the maximum number of characters that can return by the function. If main function is not returning a NULL value then this function can set maybe_=0. This function is also used to check the arguments of any function can return a NULL or not, by checking the arguments variable maybe_mull.