How to create custom functions on MySQL 5.x

An excellent feature that you could use in MySQL is the posibility of adding custom function and stored procedures. In this article I will explain how to create your own custom functions on MySQL, stored procedures are explained in another post.

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.

— From Wikipedia, the free encyclopedia

On the other hand, we have functions, they are useful when you need to compute values and you will not perform permanent environmental changes to MySQL (for example you COULD NOT USE an insert or update commands).

Here is a small example of a function for adding two variables.

DELIMITER $$
DROP FUNCTION IF EXISTS `add_function_example`$$
CREATE FUNCTION `add_function_example`(a INT, b INT) RETURNS TINYINT(1)
	DETERMINISTIC
	BEGIN

	DECLARE r TINYINT DEFAULT 0;
		SET r = a + b;
	RETURN r;
    END$$
DELIMITER ;

Explanation:

1. On line 1, we are re-defining the default delimiter in MySQL (‘;’), this line is too important in order to avoid syntax errors. 
2. Line 2, it deletes the function, if it has already defined.
3. Next line, it defines the function “add_function_example” and we use the new delimiter ($$) to separate this instruction of the next.
4. Deterministic keyword indicates this function will always return the same result  if we pass the same parameters in the input, if we don’t indicate it, NOT DETERMINISTIC is set by default.
5. Next lines are obvious 🙂
6. Last instruction will set the delimiter to ‘;’ again.

Remember, you can use the MySQL console or a web client like PHPMyAdmin in order to create your function.

Testing our function:

Go to your console or phpmyadmin or any client and test using the next statement:

SELECT add_function_example(2,3);

It is possible to get an error like:

Thread stack overrun: 8312 bytes used of a 131072 byte stack, and 128000 bytes needed. Use ‘mysqld -O thread_stack=#’ to specify a bigger stack

when you are running the previous command (see figure 1).

Figure 1. Possible error when you are running your testing command.

As you can see MySQL needs a bigger stack in order to run this kind of statements, so for solving this issue you only have to change this parameter: thread_stack in your configuration file of MySQL. In Mac OS we have this file in the next location: /Applications/XAMPP/etc/my.cfg please open it, search for thread_stack parameter and change its value to a bigger number like: thread_stack = 512K, now in order to apply this change you will need TO RESTART your MySQL server (if you are using a meta-package like XAMPP, WAMP, AppServer or something like that, this process is really simple, even if you are using a GNU/Linux system it is possible restart your MySQL server with a single command). That’s it!

Now, please return to your MySQL console o your phpmyadmin and test again using:

SELECT add_function_example(2,3);

This is all at this moment, see you next time!