Managing & Using MySQL, 2nd Edition

User-Defined Functions

UDFs are simply native programming functions. MySQL links to the libraries containing them and executes them as SQL functions in the same way you have used the PASSWORD( ) and NOW( ) functions. MySQL gives these functions access to the internals of MySQL and empowers them to manipulate data.

You will generally write UDFs in C. The examples in this chapter are all in C. However, because MySQL accesses your functions through shared libraries, you can write them in any language that you can compile into a native-code shared library. In other words, you can write a UDF in C, C++, Objective C, C#, Java, VisualBasic, or even Pascal.

MySQL supports two types of UDFs: standard and aggregate. MySQL applies standard functions to each row in the result set. PASSWORD( ) and DAYNAME( ) are examples of two common standard functions built into MySQL. If you execute the SQL in the mysql database:

SELECT UPPER(User) FROM user;

you will see the name of each user in your database in all capital letters.

An aggregate function operates on groups of rows. You generally execute aggregate functions in conjunction with the SQL GROUP BY clause. This clause causes MySQL to group result sets into sections, in which each section is a single row. An aggregate function operates on all of the values of each group. AVG( ) and MAX( ) are examples of two common aggregate functions built into MySQL:

SELECT AVG(age) FROM People GROUP BY city;

The result of this query is a single column containing ...