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
