Using Postfix with MySQL Stored Procedures? (Just Use Functions)

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!