Released this March, MemSQL 6 Beta 1 introduced MemSQL Procedural SQL (MPSQL). MPSQL supports the creation of:

  • User-Defined Functions (UDFs)
  • Stored Procedures (SPs)
  • Table-Valued Functions (TVFs)
  • User-Defined Aggregate Functions (UDAFs)

A Hidden Gem: Array Types

There’s a hidden gem in MemSQL 6 Beta 1 that we didn’t document at first — array types!  These make programming much more convenient. Since we compile your extensions to machine code, the performance is fantastic. And you don’t have to leave the comfort of your database language to get it.

To declare and initialize a new array of 10 integers, you simply say

declare a array(int) = create_array(10);

You might we wondering, how does MemSQL know what kind of array to create when you call the create_array() function?  The secret is type inference. Our compiler knows the data type of the variable you’re assigning the result of create_array() to. It uses that to determine the data type of the array.

Our arrays are zero-based, meaning the positions of the array of 10 integers above are numbered 0..9.

Real Examples

Let’s try some real examples. Here’s a function to create an array of size n containing a pseudo-random sequence of numbers:

Suppose you want to display the array — you’ll need to convert it to text:

Let’s put it all together:

MemSQL dynamically compiles code in functions. The first time (or sometimes first few times) that you run them, they can take a little longer to run due to compilation. Hence the 0.37 sec time above. Let’s see what happens if we run it again:

So, after we compile the function, it runs in milliseconds!

Speaking of performance, in our tests, a merge-sort UDF written in MPSQL can sort an array of 200,000 numbers in 0.2 seconds. Try doing that in another database language!

Multidimensional Arrays

You can declare multidimensional arrays too, like so:

declare b array(array(int));

Here’s an example function that creates a two-dimensional array and sums the contents of all the elements:>

Notice that it creates an array literal [[0, 100], [100, 0]] and assigns it to b. This is another way to make an array, as an alternative to create_array(). Our multi-dimensional arrays are called “ragged arrays” because individual sub-arrays can contain different numbers of elements, although we didn’t do that in this example.

Let’s take it for a spin:

Try MemSQL 6 Beta

Arrays are an amazing addition to MemSQL. You can learn more about arrays and MemSQL Procedural SQL in our online documentation. Download and try MemSQL 6 Beta 1 today. Enjoy!

MemSQL, Multidimensional Arrays, Array Types, MemSQL Beta Introduction