While these functions suited my needs, I’m not entirely certain that this is the best approach. Anytime you’re performing looping operations from within the database, you really ought to think hard as to whether there’s a better solution. I haven’t profiled these functions, but if you’re planning to make extensive use of them in a production environment, I’d recommend that you do so. Additionally, it could be argued that shifting the base of an integer is a presentational thing and ought to be done at the application-level anyhow.
CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0)
RETURNS varchar AS $$
DECLARE
chars char[];
ret varchar;
val bigint;
BEGIN
chars := ARRAY['0','1','2','3','4','5','6','7','8','9'
,'A','B','C','D','E','F','G','H','I','J','K','L','M'
,'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
val := digits;
ret := '';
IF val < 0 THEN
val := val * -1;
END IF;
WHILE val != 0 LOOP
ret := chars[(val % 36)+1] || ret;
val := val / 36;
END LOOP;
IF min_width > 0 AND char_length(ret) < min_width THEN
ret := lpad(ret, min_width, '0');
END IF;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE OR REPLACE FUNCTION base36_decode(IN base36 varchar)
RETURNS bigint AS $$
DECLARE
a char[];
ret bigint;
i int;
val int;
chars varchar;
BEGIN
chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
FOR i IN REVERSE char_length(base36)..1 LOOP
a := a || substring(upper(base36) FROM i FOR 1)::char;
END LOOP;
i := 0;
ret := 0;
WHILE i < (array_length(a,1)) LOOP
val := position(a[i+1] IN chars)-1;
ret := ret + (val * (36 ^ i));
i := i + 1;
END LOOP;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;