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 |
+-----------+
Hi, Justin!
On Mar 10, Justin Swanhart wrote:
>
> I am not sure of the point of that mdev. First, arrays are not table
> functions. Arrays are data structures. A table function can certainly
> return all the items in an array data structure as rows (which would be
> part of #820) but that doesn't mean that a table function could replace an
> ARRAY data type. Arrays also have to be able to be passed to stored
> routines and stored in tables too, or they are virtually useless.
Yes, the subject says "UDF returning arrays/ result set",
and the point is to have result set, not just an array.
> Second, WL#820, which far precedes that mdev, adds real table functions
> without magic "pretend" storage engine as suggested in the later mdev. In
> any case, you can't return an array or table, from a UDF, without changing
> the UDF interface which will break other UDF. The UDF interface IS NOT
> VERSIONED.
Right. But MDEV-5199 doesn't need to use or extend existing UDF
interface. It simply uses "UDF" to mean "user definable function"
without implying that it has anything to do with existing UDFs.
> wl#820 adds external stored procedures and doesn't modify the udf interface
> (UDF can rot) The mysql bug link I sent makes item functions internally
> pluggable, so they could support table functions as well and provide a
> replacement for UDF. Add an ARRAY data type and implement these patches
> (which are from Antony Curtis) and you'll get what you want.
> It would be a real shame to replace wl#820 work with some half-backed UDF
> that has not access to internals.
This is largely unrelated. This MDEV-5199 is about user functions that
can return a table. Something like
SELECT * FROM t1, tf(15, NOW()) as t2 WHERE t1.a=t2.b;
So, it's about the server that can invoke a UDF in this context and
retrieve many rows from the user function.
While wl#820 is about the language in which this user function is
written.
Regards,
Sergei