FYI: Creating User Defined Functions for MySQL
 

I found the following interesting. For better formatting or other Tips please reference the following (TIP 27) :
http://prdownloads.sourceforge.net/s...l.txt?download



Adding and Compiling a User-Defined function for Linux

Here are some examples that can be compiled and used with MySQL 4.1. See below for how to install.
First, just a taste of what they look like:


mysql> select lookup("www.yahoo.com");

+----------------------------+
| lookup("www.yahoo.com") |
+----------------------------+
| 216.109.118.71 |
+----------------------------+
1 row in set (0.02 sec)

and

mysql> select reverse_lookup("216.109.118");
+-------------------------------------+
| reverse_lookup("216.109.118.79") |
+-------------------------------------+
| p16.www.dcn.yahoo.com |
+-------------------------------------+
1 row in set (0.02 sec)


Also

mysql> select sequence(3);

+-------------+
| sequence(3) |
+-------------+
| 4 |
+-------------+
1 row in set (0.01 sec)

mysql> create table junk (a varchar(1));
mysql> insert into junk (a) values ('a'),('b'),('c'),('d'),('e');



mysql> select sequence(),a from junk;
select sequence(),a from junk;
+-----------------+------+
| sequence() | a |
+-----------------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+-----------------+------+
5 rows in set (0.00 sec)





An example of an aggregate function:


mysql> create table cost (inum int, cost double);

mysql> insert into cost (inum,cost) values (3,4.5);

mysql> insert into cost (inum,cost) values (10,21.3);

mysql> select avgcost(inum,cost) from cost;
select avgcost(inum,cost) from cost;
+--------------------+
| avgcost(inum,cost) |
+--------------------+
| 17.4231 |
+--------------------+
1 row in set (0.00 sec)


OK, HOW TO COMPILE:

For best results mysql-4.1.2-alpha or above configured with the following option:

--with-mysqld-ldflags=-rdynamic

The full line of my configure is as follows since I use openssl as well.

STEP A:

./configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags=-rdynamic

make

make install

/etc/init.d/mysql restart

( Note: if "/etc/init.d/mysql" does not exist see TIP 20,
http://prdownloads.sourceforge.net/s...l.txt?download )



STEP B:

Under "mysql-4.1.2-alpha/sql" issue the following command.

# make udf_example.so

STEP C:

As root copy the file to a directory where mysql can find it.

# cp udf_example.so /usr/lib/udf_example.so


STEP D:

Load mysql. You may want to look at udf_example.cc, since it as instructions
as well. From here issue the following commands:



mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
mysql> CREATE FUNCTION sequence RETURNS INTEGER SONAME "udf_example.so";
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so";


And the functions can be deleted by the following:

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
mysql> DROP FUNCTION avgcost;


That's it. Just run the functions above. These can serve as templates for writing
your own.

Anyway, I found the following interesting, since it comes with the Linux src and seems to
work well on 4.1.2.

Regards,

Mike Chirico