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 the average of all age values for people who live in the same city.
You create aggregate and standard functions in much the same manner—aggregate functions simply have two extra routines.