As an alternative to some features that SingleStore’s MPSQL does not allow, I would like to implement some logic as UDFs or procedures and define them as external functions. In some cases, if the select statement can be defined as an external function without defining a function or procedure, that seems sufficient.
I want to use this defined external function in the insert select statement.
I activated the data api and checked that select can be executed using it, but I couldn’t find a way to connect it to an external function.
I defined following function and procedure and work like this.
create or replace function get_rand()
returns varchar(100) as
begin
return replace(uuid(), ‘-’, ‘’);
end;
singlestore> select get_rand();
+----------------------------------+
| get_rand() |
+----------------------------------+
| 65955f97fc7a4389aacd881561537b08 |
+----------------------------------+
and data api is enabled to remote access, and it works I check it with curl command.
$ curl -s -u “root:xxxxxxx” -H “Content-Type: application/json” --data ‘{“sql”: “SELECT get_rand()”, “database”: “jnshin”}’ http://myip:3305/api/v2/query/rows
{“results”:[{“rows”:[{“get_rand()”:“5bc4acf6044c4cd6a19d413adfad69e8”}]}]}
I could see an example of defining a lambda function as an external functioin in the manual. However, in the case of data api, I don’t know how to define it.
After activating the External function feature and defining the link, I tried to call the function as follows, but an error occurs because SQL cannot be defined.
CREATE LINK link_uuid AS HTTP CREDENTIALS ‘{“headers”: {“Authorization”: “Basic XXXXXXXXX”, “Content-Type”: “application/json”}}’;
create or replace external function get_uuid(sqlstmt varchar(100)) returns varchar(100) as remote service
‘http://myip:3305/api/v2/query/rows’ format json link link_uuid;
select get_uuid(“select get_rand()”);
ERROR 2578 (HY000): Error 1065: Query was empty
I thought I could access remote function with external function and data api feature, but I didn’t find how to post a query in external function, or how to call remote function.
Is it possible?
Is there anyone who tried to similar way, please let me know how to do.
Thanks.
jaenyun