extension functions

[hnuhchen]

Hello. I an using mapd-core-4.6.0 by source code. I notice that in the source file ExtensionFunctions.hpp, there are many mathematical functions can be used by sql statements like Cos, Acos and so on. It can be used directly by sql like select cos([key]) from [table]; The type of the key can be boolean, double or other types. But i dont know how to use functions like _ST_LengthLineString, the first parameter of the functions is _int8t*, what does it represents? An array? And how to use it by sql?

Besides, if i want to add an extension function which can use char* parameters, what should i do? Thank you very much.

[alexbaden]

Hi @hchnu ,

The ExtensionFunctions.hpp file contains both scalar extension functions (like cos(x)) and variable length (or nonscalar) extension functions, like ST_Length_LineString. For the scalar functions, we automatically register the function with our parser using the function definition (so cos(double x) would be registered as cos which takes a double argument type). For the varlen functions, we typically will register a function manually in the MapDSQLOperatorTable.java file, then have our translator (RelAlgTranslator.h) translate the parser definition into the appropriate arguments for the function.

For example, if you had a function called array_length Which returned the number of elements in an array, you might do something like:

int64_t array_lenght(int8_t* per, size_t sz) { 
  return static_cast<int64_t>(sz);
}

You would then register array_length in the MapDSQLOperatorTable.java file.

Note that you might have to play with the pointers a little bit, but that’s the general idea.

The ST_Length_LineString Function is designed to work with our geospatial runtime, so it has additional parameters (like compression mode, SRID, etc) that need to be passed to the function. The RelAlgTranslator handles that, with most of the code in RelAlgTranslatorGeo.hpp.

We had a tutorial on adding geospatial extension functions floating around. I will try and dig it up for you if that’s something you are interested in.

[hnuhchen]

Hi,@alexbaden. Thank you for your answer. If i have a table test(data1 text encoding none, data2 text encoding none). And i want to use an extension function to operate data1 and data2(such as data1 > data2, data1 < data2 or other operations). Then how to define the extension function?
Whether i can define the function like this?

EXTENSION_NOINLINE double FunctionTest(int8_t* data1,int8_t* data2) {
  ......
}

And then execute the sql select FunctionTest(data1,data2) from test;

If i define the extension function like this, will the sql be executed successfully? Or i thould modify the file RelAlgTranslator.h to support the translation?