convert very large unsigned numbers to base62?

Поиск
Список
Период
Сортировка
От D. Dante Lorenso
Тема convert very large unsigned numbers to base62?
Дата
Msg-id 482B7FAF.7000902@lorenso.com
обсуждение исходный текст
Ответ на Re: Need timestamp function that will change within a transaction  (Dimitri Fontaine <dim@hi-media.com>)
Ответы Re: convert very large unsigned numbers to base62?
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Martin Marques
Дата:
Сообщение: bug on ALTER TABLE
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: psql \pset pager