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;
May I use this in some commercial work I’ve been doing? If so may I make changes if attribution is given?
May I use these in a commercial project? If so may I make changes if attribution is provided?
Certainly. You may use it in anyway you wish; no attribution is necessary. Though, as I mentioned in the original posting, you may want to test the performance of the functions or maybe even reconsider whether this is a task that should be done within the DB.
Would you have a C equivalent of this? Doing this in pl/pgsql is going to be a major hit.
Thanks!
Your base36_decode contains bug in string:
ret := ret + (val * (36 ^ i));
For large numbers (for example,
select * from base36_decode(’324adc6a6ds’));
it returns
ERROR: invalid input syntax for integer: “1.11836923491136e+16″
CONTEXT: PL/pgSQL function “base36_decode” line 17 at assignment
Mentioned string must be fixed as follows:
ret := ret + (val * (36 ^ i))::bigint;
After that all works ok, thanks for sharing your code.
Another limitation of this code is – it supports only half of possbible bigint values (only positive ones). When ret overflows – function returns exception.
Thanks for the feedback!