The box.internal.sql_function_create is forbidden

[TarantoolBot]

Legacy mechanism box.internal.sql_function_create to make some
Lua function available in SQL is forbidden now.

To make some function available in SQL you need to use
box.schema.func.create() mechanism: you need to specify

1. function language and language-specific options(e.g. you
   are able to define a persistent Lua function)
2. whether this function is_deterministic or not: deterministic
   functions allows to generate more efficient SQL VDBE bytecode
   so you better specify it when it is true
3. the function returns type: a Tarantool type string describes
   a type of value returned by function
4. param_list - a table of Tarantool's types strings desccribe
   function argument types
5. exports - a table of Tarantool's frontends where this function
   should be available ('LUA' by default). You need to specify
   {'LUA', 'SQL'} to make function available both in SQL requests
   and visible in box.func folder

Example:
-- Case1: C function
-- function1.so has int divide() symbol
box.schema.func.create("function1.divide", {language = 'C',
returns = 'number',
param_list = {'number', 'number'},
is_deterministic = true,
exports = {'LUA', 'SQL'}})
box.execute('SELECT "function1.divide"(6, 3)')

• metadata:
  • name: '"function1.divide"(6, 3)'
    type: number
    rows:
  • [2]
    box.schema.func.drop("function1.divide")

-- Case2: Persistent Lua function
box.schema.func.create("SUMMARIZE", {language = 'LUA',
returns = 'number',
body = 'function (a, b) return a + b end',
param_list = {'number', 'number'},
is_deterministic = true,
exports = {'LUA', 'SQL'}})
box.execute('SELECT summarize(1, 2)')

• metadata:
  • name: summarize(1, 2)
    type: number
    rows:
  • [3]
    box.schema.func.drop("summarize")

Moreover there is a special predefined Lua function LUA that
allows to evaluate a custom Lua expressions in SQL.
You need to pass a string in form "return ...." to LUA function
that returns more than one value of any type.

Example:
box.execute('SELECT lua('return 1 + 1')')

• metadata:
  • name: lua('return 1 + 1')
    type: any
    rows:
  • [2]
    box.execute('SELECT lua('return box.cfg.memtx_memory')')
• metadata:
  • name: lua('return box.cfg.memtx_memory')
    type: any
    rows:
  • [268435456]
    Requested by @kshcherbatov in tarantool/tarantool@d4a7459.

[pgulutzan]

Drafted. Waiting for approval.

[Totktonada]

@pgulutzan, we need description of this feature to link it from 2.3.1 release notes (to avoid quite big description in the release notes itself). Can you share commit, which handles this issue? I think I can review it from correctness point of view and it should be good enough for now.

@artembo, can you help with processes? Where we should land the commit to have it in 2.3 documentation? Will it land also to 2.2 documentation: in other words, should we explicitly mention that it is 2.3-only feature in the text itself?

[artembo]

@Totktonada, just commit the changes to appropriate branches and it will be delivered to tarantool.io within one hour. CI is set up for 1.6, 1.10, 2.2 and 2.3 branches which correspond the documentation versions on the website.

@lenkis, is mentioning version-specific peculiarities required in the documentation text itself?

[pgulutzan]

@Toktonada: Sorry, all changes that I made in version 2.3 since December 8 have disappeared. I guess that @lenkis has done some moving and restructuring. I will ask her what is going on.

[Totktonada]

@artembo Thanks!

@lenkis, is mentioning version-specific peculiarities required in the documentation text itself?

If there is the separate 2.3 branch, I guess the answer should be 'no'. When we discussed it with Lena, it did not exists.

[pgulutzan]

@Totktonada, Today I recovered and pushed the material into doc branch pgulutzan-2.3
1147d2b
To see the sql.rst file one must click "Load diff" ("Large diffs are not loaded by default"),
then look for the string func.create. @lenkis tells me that this patch still needs approval from K. Yukhin.

[pgulutzan]

@Totktonada, documentation is now pushed, in Calling Lua routines from SQL
https://www.tarantool.io/en/doc/2.3/reference/reference_sql/sql/#calling-lua-routines-from-sql
(there is a pointer to the section from box.schema.func.create).
The lua() function is also in what was pushed, in the functions section.
And there are several box.schema.func.create examples in the later section
"Lua functions to make views of metadata".
If I hear no major complaints soon, I will close this.

[pgulutzan]

I heard no major complaints soon, so I close this.