Base36 Conversion in PostgreSQL

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;