maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

wl#820 also has table functions in it:
http://antbits.blogspot.com/2009/01/table-functions-in-mysql.html

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)

On Tue, Mar 10, 2015 at 1:45 PM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:

> 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
>