Objects in SingleStore, Part 3

Storing compiled code inside the system is always good to achieve performance and business objectives. SingleStore has many different procedural objects, which store compiled code. Stored procedures and functions are often the best — and most favored — objects of many developers. Here, I will go through the high level of procedural objects in SingleStore. 

This is the third and final article in a series for objects in SingleStore. Check out part one for table types, and part two for indexes. 

Procedural Objects

Let's talk about procedural objects in SingleStore. Stored procedures and functions are part of objects in SingleStore, working like other programmatic logics which store compiled code inside the database. It can take controlled statements like, IF LOOP, WHILE LOOP and FOR LOOP. SingleStore has stored procedures, user-defined scalar-value functions (UDFs), user-defined table-valued functions (TVFs) and user-defined aggregate functions (UDAFs).

Stored Procedure

A stored procedure is a callable routine that accepts input parameters, executes the programmatic logic and optionally returns a single value. A stored procedure can manipulate data in a table in SingleStore. It can return a single value like a UDF, as well as return a query-type value. Stored procedures allow scalar data types, non-scalar data types (ARRAY and RECORD) and query data types as input parameters. Please see SingleStore documentation for valid data types. SingleStore supports temporary stored procedure, which is a stored procedure that is available during the user's session — and created using CREATE TEMPORARY PROCEDURE syntax.

Functions (UDF and TVF):

A wide range of functions are provided by SingleStore. But if a customer logic set is required, then UDF is the best solution (i.e., prime number is odd or even). A UDF is a callable routine that accepts input parameters, executes programmatic logic and returns a value (or values). 

User defined table-valued function (TVF) is a callable routine that accepts input parameters, executes a single SELECT statement in the function body and returns a single table-type value (similar to a view). TVFs can reference other TVFs in the function body. When a referenced TVF is executed inside the function body of a TVF, the result set can be queried like a table:

CREATE FUNCTION tvf_1(a INT)
  RETURNS TABLE AS RETURN
    SELECT * FROM table_1 limit a;
    LIMIT a;

CREATE FUNCTION tvf_2()
  RETURNS TABLE AS RETURN
    SELECT * FROM tvf_1(10)
    GROUP BY col1
    ORDER BY col1
    LIMIT 10;

SELECT * FROM tvf_2();

External Functions:

External functions are an extension of SingleStore’s internal user defined and table value functions. External functions are maintained and executed in services outside of SingleStore.

CREATE [OR REPLACE] EXTERNAL FUNCTION [<database_name>.]function_name ([<parameter_list>])
RETURNS <return_type>
AS REMOTE SERVICE "<service_endpoint>"
FORMAT <data_format> [LINK [database_name.]<connection_link_name>];

A call to an external UDF can be made anywhere in a SQL statement where an expression is allowed. For example:

SELECT example_external_udf(100,'foo');

SELECT * FROM t WHERE example_external_udf(100,'foo') = 'bar';

Details about external functions are available in SingleStore docs. 

Aggregates (UDAF)

User-defined aggregate function (UDAF) is a callable routine that accepts input parameters, executes programmatic logic in the function body and returns a scalar-type value. 

CREATE [OR REPLACE] AGGREGATE function_name ( [parameter_list] )
  RETURNS{data_type [data_type_modifier]} WITH STATE data_type
  INITIALIZE WITH udf_function_name
  ITERATE WITH udf_function_name
  MERGE WITH udf_function_name
  TERMINATE WITH udf_function_name ;

As syntax shows, the name of each UDF function executes for the INITIALIZE WITH, ITERATE WITH, MERGE WITH and TERMINATE WITH clauses. 

• The INITIALIZE function takes in no arguments, and returns a STATE data type 
• The ITERATE function takes in a STATE data type and the input parameter data type, and returns a STATE data type. If the UDAF has n parameters, the ITERATE function will take in n+1 arguments, with the first argument being the STATE type. 
• The MERGE function takes in two STATE data types, and returns a STATE data type. 
• The TERMINATE function takes in a STATE data type, and returns the type specified in the RETURNS clause.

Details about aggregate function are available in SingleStore documentation.

Interested in more from SingleStore? Visit SingleStore Docs for additional information and knowledge about executing SingleStore functionalities.