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.

5 comments:

arjenAU said...

Nice!

Roland Bouman said...

Hi!

awsome! and how fast you pulled it off ;)

/me bows and applauds ;)

Amaranathareddy said...

Hey i am getting error message like 'psm_example.so is not availble when i try to execute the same Command.Can you please explain little bit more clear

Error:

INSTALL PLUGIN Deep_Thought SONAME 'psm_example.so';
ERROR 1126 (HY000): Can't open shared library 'C:\Program Files\MySQL\MySQL Serv
er 5.5\lib/plugin/psm_example.so' (errno: 2 )


Thanks in advance

Amaranathareddy.M

Antony said...

I must admit, I have never tried to build the branch on Windows... If you're using the standard downloads, this will not work.

Lukas Eder said...

That's something I had been looking for, for a while. How did your presentation at the conference go? Does it look as though this kind of support will make it to the MySQL core?