From: | "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com> |
---|---|
To: | Postgres-General <pgsql-general(at)postgresql(dot)org> |
Subject: | convert very large unsigned numbers to base62? |
Date: | 2008-05-15 00:11:27 |
Message-ID: | 482B7FAF.7000902@lorenso.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'd like to convert very large unsigned numbers (ala bigint) to a text
string using base62. I created this PL/PERL function to do the trick:
--------------------
CREATE OR REPLACE FUNCTION "public"."ls_crypt_convert_base" (in_value
text, in_base integer) RETURNS text AS
$body$
my ($value, $base) = @_;
$base = ($base > 62) ? 62 : (($base < 2) ? 2 : $base);
my @nums = (0..9,'a'..'z','A'..'Z')[0..$base-1];
my $index = 0;
my %nums = map {$_, $index++} @nums;
# short circuit if no value
$value =~ s/\D//g;
return if ($value == 0);
# this will be the end value.
my $rep = '';
while ($value > 0) {
$rep = $nums[$value % $base] . $rep;
$value = int($value / $base);
}
return $rep;
$body$
LANGUAGE 'plperl' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
--------------------
# SELECT ls_crypt_convert_base(999999999999999999::text, 62);
ls_crypt_convert_base
-----------------------
1bS0EMtBbK8
(1 row)
# SELECT ls_crypt_convert_base(888888888888888888::text, 62);
ls_crypt_convert_base
-----------------------
13F7tmqjhmu
(1 row)
# SELECT ls_crypt_convert_base(7777777777::text, 62);
ls_crypt_convert_base
-----------------------
8umLiF
(1 row)
# SELECT ls_crypt_convert_base(123456789::text, 62);
ls_crypt_convert_base
-----------------------
8m0Kx
(1 row)
Did I just reinvent the wheel? It seems like something like this is
should already be built into PostgreSQL and I just don't know where to look.
-- Dante
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2008-05-15 00:22:15 | Re: psql \pset pager |
Previous Message | Martin Marques | 2008-05-14 23:35:14 | bug on ALTER TABLE |