If you’re like me and want to use Postfix with a MySQL backend, you may have also wanted to be able to call stored procedures from Postfix.
Unfortunately, MySQL stored procs return multiple resultsets while Postfix’s call to the MySQL C API can only process a single resultset. I banged my head against the wall for a while until I realized that a function called from a SELECT statement would return a Postfix-friendly single resultset.
For example, this would be a perfectly acceptable virtual alias map file:
user = mailreader password = somepassword dbname = mail_config query = SELECT retval FROM (SELECT fnPostfixVirtualAliasMapGet('%s') AS retval) t WHERE t.retval IS NOT NULL; hosts = 127.0.0.1
This is useful if, for example, you wanted to track your customers’ usage in realtime. Then your function might look like this:
FUNCTION `fnPostfixVirtualAliasMapGet`( p_alias VARCHAR(128) ) RETURNS varchar(128) CHARSET latin1 BEGIN DECLARE v_destination VARCHAR(128) DEFAULT NULL; DECLARE v_zone INTEGER DEFAULT NULL; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @garbage = 1; SELECT destination, zone INTO v_destination, v_zone FROM postfix_virtual_alias_maps WHERE alias = p_alias; IF v_zone IS NOT NULL THEN INSERT INTO usage_data.mx ( zone, created_on ) VALUES ( v_zone, NOW() ); END IF; RETURN v_destination; END
Of course, your implementation could vary.
There is one big gotcha. Postfix requires either no resultset (meaning no match found), or a resultset (meaning a match was found). If you were to use the simpler form of the example…
query = SELECT fnPostfixVirtualAliasMapGet('%s');
…Postfix would always think that a match was found for that lookup because the function would return NULL. That’s why the query is more complex, and the function’s result is used as a derived table.
Also notice this line in the function:
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET @garbage = 1;
The purpose of the continue handler is to ignore SQLSTATE ‘02000’, which is ER_SP_FETCH_NO_DATA, or in more straightforward terms is the warning “No data – zero rows fetched, selected, or processed“. This warning is generated when the SELECT … INTO [variables] returns no data, and the variables are left unchanged. I experienced problems with Postfix when this warning was generated (and it’s just going to fill up logs anyway).
The SET @garbage = 1; is just a throwaway call to give the continue handler something to do. I don’t know if it’s needed, but there you have it.
That’s about it. Enjoy!