MariaDB Stored Procedures/Routines/Programs/Functions

Contents

I wrote these notes because the MySQL/MariaDB documentation uses Stored Procedure, Stored Routine, Stored Program, and Stored Function, and I wanted to tease out the difference between them.

MariaDB has two statements for creating stored entities:

• CREATE PROCEDURE - A PROCEDURE is invoked using CALL
• CREATE FUNCTION - FUNCTION is invoked using SELECT function-name(params)

In the MariaDB documentation, “Stored Procedures” is a topic under “Stored Routines” in Programming and Customizing MariaDB.

Stored Procedures (provided by MariaDB)

Three stored procedures are provided by MariaDB for use with COLUMNSTORE_INFO schema:

• total_usage()
• table_usage()
• compression_ratio()

Stored Routines/Stored Programs

The term “Stored Program” appears to be an older term for what are now called “Stored Routines.” Indeed at one point in the documentation there’s the line:

CREATE ROUTINE - [Privilege for creating] Stored Programs using the CREATE PROCEDURE and CREATE FUNCTION statements

Stored routines can be short or very long, contain multipple SQL statements, and include control structures such as

Stored Functions

A stored function operates as a function call; for example, my_function in the statement below could be used to lowercase all the words in the passed text and then flip the initial letter of each word to uppercase, save for words with three or fewer characters:

SELECT id, my_function(p.name) ... FROM persons AS p WHERE ...

A stored routine for selecting a name based on a telephone number

I investigated stored routines because as part of my PBX Whitelist-AGI project I wanted to query my Nextcloud contacts list for a telephone number. I ran into a limitation on FreePBX’s MySQL CallerID Lookup source, in that the maximum query length is a mere 255 characters. But I needed a more complex query that would select a telephone number and a name (that’s why the LEFT JOIN), and on no match return LOOKUP_FAILED original name. That part was needed because a CID Lookup Source of type MySQL doesn’t differentiante between a successful lookup and a failed one.

As it turned out the 255 character limit was moot. I couldn’t write a simple query that simultaneously did a SELECT and LEFT JOIN and set a NULL name to the LOOKUP_FAILED value: I got either an empty name or the result from the LEFT JOIN. I needed a second query to check for a NULL name. That alone made a stored procedure necessary.

DELIMITER //

CREATE PROCEDURE getNameFromPhoneNumber (IN phone_number CHAR(15), IN original_name CHAR(50))
  READS SQL DATA
  COMMENT 'Given a phone number (10 digits, or 11 starting with "1"), returns a name'
BEGIN
  SELECT cp2.value INTO @name
  FROM oc_cards_properties AS cp1 
  LEFT JOIN oc_cards_properties AS cp2 
    ON cp2.addressbookid=cp1.addressbookid AND cp2.cardid=cp1.cardid AND cp2.name='FN'
  WHERE cp1.name='TEL' AND (
    cp1.value LIKE CONCAT('%', phone_number)
    OR cp1.value LIKE CONCAT('%', phone_number-10000000000)
  );  
  SELECT IF (@name IS NULL, CONCAT('LOOKUP_FAILED ', original_name), @name) AS name;
END;
//

DELIMITER ;
GRANT EXECUTE ON PROCEDURE getNameFromPhoneNumber TO asterisk@pbx;

Note that the above MySQL code has a statement to grant EXECUTE permission.

In FreePBX > CallerID Lookup Sources > MySQL I set the query as follows:

CALL getNameFromPhoneNumber('[NUMBER]', '${CALLERID(name)}');