I’m working with MariaDB 10.6.22 on Ubuntu 24.04 and created a custom function to remove accents from text fields. The function works perfectly when I call it directly on existing data, but I’m having trouble using it in a generated column definition.
When I try to create a table like this:
CREATE TABLE user_data(
id BIGINT NOT NULL AUTO_INCREMENT,
full_name TEXT,
birth_year INTEGER DEFAULT 0,
category TEXT DEFAULT NULL,
normalized_name TEXT GENERATED ALWAYS AS (remove_accents(full_name)) STORED,
PRIMARY KEY(id)
);
I get this error: ERROR 1901 (HY000): Function or expression 'remove_accents()' cannot be used in the GENERATED ALWAYS AS clause
Here’s my custom function:
DELIMITER $$
CREATE OR REPLACE FUNCTION remove_accents(input_text TEXT)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE processed_text TEXT;
DECLARE accent_chars TEXT;
DECLARE normal_chars TEXT;
DECLARE char_position INT;
SET processed_text = input_text;
SET accent_chars = 'ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïñòóôõöùúûüý';
SET normal_chars = 'AAAAAACEEEEIIIINOOOOOUUUUYaaaaaaceeeeiiiinoooooouuuuy';
SET char_position = LENGTH(accent_chars);
WHILE char_position > 0 DO
SET processed_text = REPLACE(processed_text,
SUBSTRING(accent_chars, char_position, 1),
SUBSTRING(normal_chars, char_position, 1));
SET char_position = char_position - 1;
END WHILE;
RETURN processed_text;
END$$
What am I missing to make this work with generated columns?