Search This Blog

14 January 2009

Table functions in MySQL

Less than 48 hours after starting to scratch this itch, I have table functions working in my WL820 repository on Launchpad.

It is pretty nifty:
mysql> INSTALL PLUGIN Deep_Thought SONAME 'psm_example.so';
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE FUNCTION test.FooAnswer()
-> RETURNS TABLE(answer TEXT)
-> NO SQL LANGUAGE Deep_Thought EXTERNAL NAME 'compute';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE VIEW foobar AS SELECT * FROM TABLE(test.FooAnswer) AS wibble;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM foobar WHERE answer LIKE 'F%';
+-----------+
| answer |
+-----------+
| Forty-Two |
+-----------+
1 row in set (0.01 sec)


I'm quite pleased with the result. Now I can start playing with it and I expect to make use of it for my presentations at the MySQL conference this April.