Writing UDFs

UDFs can be of two types, normal and aggregate. Normal UDFs take inputs and deliver an output just like an ordinary function in most programming languages. When run on a set of rows, they will return a result for every row. Aggregate UDFs take a group of rows, process each row, and produce a result at the end. In other words, they will return one result per group. Therefore, aggregate functions are useful for tasks such as adding up a group of values or calculating an average.

Whether MySQL considers a given UDF as an aggregate or a normal type depends on how it was installed. However, the API is somewhat different too, and we need to take care to install UDFs that use the aggregate API as aggregate and normal UDFs as normal. Otherwise they will not only fail to work correctly, but may as well crash the whole server.

Why write UDFs

There are several advantages and disadvantages to UDFs that we should be aware of. UDFs are much easier to develop than is hacking raw code into the...