This is a part of MDEV-5199 (Table functions) which addresses table functions that are defined in SQL (as opposed to table functions that are implemented in C++ and loaded as .so/.dlls).
This task is a GSOC '15 project of diwas joshi not anymore
(note: the following differs from description in MDEV-5199, and this is the latest text).
We will use SQL Server's variant of syntax, where CREATE FUNCTION explicitly mentions function name. To define a table function one will use:
Inside function body, table table_name is visible as a temporary table. The table is initially empty. The code of the function is expected to insert rows into table_name. When the function finishes, the table will be returned to outside as the function's result.
Table function will create and populate a temporary table (either HEAP or Aria/MyISAM, just like it is done for other kinds of temporary tables). Once the table function has finished populating the temporary table, it will be returned to the statement that invoked the table function. The caller statement will then read the table.
In other words, we will not support any kind of "streaming" for results of table function.
Trivial functions (with the body of a single INSERT ... SELECT) can be treated as a VIEW. It is not clear whether this should be implemented in the scope of this WL entry.
Table functions must have constant parameters. One can't use things like
This is because our implementation will pre-populate table function tables once per query execution. MySQL/MariaDB optimizer doesn't support tables that may change their contents during query execution.
The parser should support all proposed syntax.
note: the table can only be addressed through an alias. This syntax is not
one must use
When we find a table reference in the FROM clause, we should make
a lookup in mysql.proc, make appropriate checks, then open and parse
We should be able to take the "RETURNS ...." statement and produce a TABLE
object from it. This is needed for doing name resolution on the other parts
of the "caller" query .
When one defines a stored function:
All statements inside the body of the function (i.e. stmt1, stmt2, etc) must use a modified name resolution process where table table_name resolves to the table that will be returned by the function. This may shadow the real table with name table_name.
How does table functions should be stored in mysql.proc table. Are they a special kind of
- "SELECT sp_func()" should return error for table functions