Epic SQL: DIGEST_FORWARDING (used in my Postfix instances)

DIGEST_FORWARDING.sql

DROP FUNCTION DIGEST_FORWARDING;

DELIMITER //

CREATE FUNCTION DIGEST_FORWARDING (s VARCHAR(80))
RETURNS TEXT
DETERMINISTIC
LANGUAGE SQL
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT 'Takes an email address, potentially an alias, and returns a real mailbox'
BEGIN
    DECLARE addr TEXT;
    SET addr = NULL;
    SELECT (CASE WHEN f.allow_wildcard_goto = 0 THEN f.goto WHEN f.allow_wildcard_goto != 0 THEN REPLACE(REPLACE(f.goto, '%@', SUBSTRING(s, 1, LOCATE('@', s))), '@%', SUBSTRING(s, LOCATE('@', s))) END) INTO addr FROM forwardings f WHERE (f.goto != s OR f.recursive != 0) AND s LIKE f.address AND (f.allow_wildcard_goto != 0 OR f.allow_restricted_goto != 0 OR 1 IN (SELECT 1 FROM domains d JOIN mailboxes m WHERE f.goto = CONCAT(m.email, '@', d.domain) AND d.active + m.active + f.active = 3)) ORDER BY f.priority DESC LIMIT 1;
    RETURN addr;
END//

DELIMITER ;

SELECT DIGEST_FORWARDING('[email protected]');