Stored Procedures and Stored Functions with MariaDB Xpand

Overview

Stored routines can be used to execute procedural code on the database server. Stored routines refers to stored procedures and stored functions. MariaDB Xpand supports both stored procedures and stored functions.

Stored Procedures and Stored Functions

MariaDB Xpand supports both stored procedures and stored functions.

Languages

MariaDB Xpand supports the following languages for stored procedures and stored functions:

  • MariaDB SQL/PSM

Synchronization

MariaDB Xpand automatically synchronizes stored routines on all Xpand nodes.

Unsupported Statements

MariaDB Xpand does not support the following statements in stored routines:

Unsupported Stored Routine Features

  • Altering a routine

  • Creating a view which calls a stored routine - Xpand will permit the creation of the view, but querying the view is unsupported.

  • Creating a prepared statement that calls a stored routine

Special Handling

Duplicate Labels

MariaDB Xpand does not report an error when a label name is used multiple times in a stored routine.

Call Stored Procedure from Prepared Statement

MariaDB Xpand does not support calling a stored routine from a prepared statement.

Other Caveats

  • MariaDB Enterprise Server does not allow dynamic SQL in stored routines, but in Xpand you can use SQL prepared statements (PREPARE, EXECUTE) in stored routines.

  • When creating a table with a trailing select statement, (CREATE ... SELECT), the following data types do not translate properly:

    • BIT type will always be BIT(64)

    • DECIMAL type will become VDECIMAL

    • ENUM and SET types will always be VARCHAR(256)

  • Storing a TIMESTAMP in a session variable yields 0000-00-00 00:00:00.

  • ClustrixDB allows you to create a stored procedure with the same name as system built-in procedures. MariaDB Enterprise Server does not permit this.

    Example: CREATE PROCEDURE pi(); BEGIN; END; does not error in Xpand.

  • Xpand allows DROP or ALTER of another stored routine from within a stored routine