According the the documentation, MariaDB allows user defined functions to be used in generated column definitions https://mariadb.com/kb/en/generated-columns/
User-defined functions (UDFs) are supported in expressions for generated columns. However, MariaDB can't check whether a UDF is deterministic, so it is up to the user to be sure that they do not use non-deterministic UDFs with VIRTUAL generated columns.
I'm using MariaDB 10.3.20 and I've created a deterministic function but when trying to create the field, it fails. Here is some sample code.
CREATE TABLE `json_virt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`json_arr` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;
INSERT INTO json_virt SET json_arr = '["a","b"]' ;
INSERT INTO json_virt SET json_arr = '["c","d"]' ;
DELIMITER //
CREATE OR REPLACE FUNCTION `test`.`json_implode`(`data` TEXT, `sep` TEXT)
RETURNS text
CHARSET utf8mb4
COLLATE utf8mb4_unicode_ci
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
DECLARE i INT UNSIGNED DEFAULT 0 ;
DECLARE v_count INT UNSIGNED DEFAULT JSON_LENGTH(data) ;
DECLARE v_current_item BLOB DEFAULT NULL ;
DECLARE v_current_path BLOB DEFAULT NULL ;
DECLARE sep_length INT UNSIGNED DEFAULT CHAR_LENGTH(sep) ;
DECLARE str TEXT DEFAULT '' ;
WHILE i < v_count DO
SET v_current_path = CONCAT('$[', i, ']') ;
SET v_current_item = JSON_EXTRACT(data, v_current_path) ;
SET v_current_item = JSON_UNQUOTE(v_current_item) ;
SET str = CONCAT(str, sep, v_current_item) ;
SET i := i + 1;
END WHILE;
SET str = SUBSTRING(str, sep_length+1) ;
RETURN str ;
END //
DELIMITER ;
SELECT
json_virt.*,
json_implode(json_virt.json_arr, ', ') AS json_imp
FROM json_virt
;
ALTER TABLE `json_virt` ADD `json_str` TEXT AS (json_implodex(json_arr, ', ')) VIRTUAL ;
ALTER TABLE `json_virt` ADD `json_str1` TEXT AS json_implode('["x","y"]', ', ') VIRTUAL ;
The json_implode() function works nicely as you can see by the SELECT statement, but when trying to do the last two ALTER TABLE statements, both fail.
I'm interested in this from a few different angles:
I see a lot of developers using "UDF" when they mean what MySQL/MariaDB calls a Stored Function. That is, a routine written in a language based on ANSI SQL and deployed dynamically on a running MySQL Server.
Whereas the term UDF is used by MySQL/MariaDB for a completely different feature.
https://mariadb.com/kb/en/create-function-udf/ says:
A user-defined function (UDF) is a way to extend MariaDB with a new function that works like a native (built-in) MariaDB function such as ABS() or CONCAT().
UDFs need to be written in C, C++ or another language that uses C calling conventions, MariaDB needs to have been dynamically compiled, and your operating system must support dynamic loading.
Note that Microsoft SQL Server uses UDF for something more like MySQL/MariaDB's Stored Function. This might be the source of the confusion.