External function

Help Procedural SQL
1

Hi Guys,
I created microservice to select data from PostgreSQL db. This microservice receives SQL query as input parameter with name sqlquery and returns json.
It works e.g. see:

curl --location --request POST “http://localhost:3003/dbquery” --data-urlencode “sqlquery=SELECT * FROM public.players WHERE id < 3”
Result: “[{"id":1,"name":"Petya"},{"id":2,"name":"Stepa"}]”

Then I created external functon (with necessary previous settings):
SET GLOBAL enable_external_functions = ON;
SET GLOBAL external_functions_allowlist = ‘{“endpoints” : [“http://host.docker.internal:3003/dbquery”]}’

CREATE OR REPLACE EXTERNAL FUNCTION get_external_data(sqlquery LONGTEXT)
RETURNS LONGTEXT
AS REMOTE SERVICE ‘http://host.docker.internal:3003/dbquery’
FORMAT JSON;

SELECT get_external_data( ‘SELECT id, name FROM public.players’) AS res;

But there I see Error Code: 2601. External function runtime failure: Malformed JSON at offset 0

What should I add/change ?

(Apropos, I do it to fix missed feature - foreign/external/linked servers; it this feature planned already ?)

Thanks!

2

@ schristian,
can you advice ?

3

The json you return needs to be an object with the “data” tag.
The json within the “data” object is an array of arrays with no tags, like this:

{“data”: [
[0,“Petya”],
[0,“Stepa”]
]}

Also note that since only 1 request was sent to your service, all the rows you return should be for the same id sent to you (which would be 0).

4

@ schristian, thank you.
So, on now it’s impossible to use own and external post requests there and for same functionality it’s necessary to create very specific…
Plan Singlestore change it ?