Мне нужно преобразовать строки, хранящиеся в моей базе данных, в числовое значение. Результат может быть целым (предпочтительным) или Bigint. Это преобразование должно выполняться на стороне базы данных в функции PL/pgSQL.
Может ли кто-нибудь указать мне на какой-то алгоритм или любой API, который можно использовать для достижения этого?
Я искал это в Google уже часами, пока не нашел ничего полезного :(
Просто сохраните первые 32 бита или 64 бит хэша MD5. Конечно, это исключает основное свойство md5 (= вероятность того, что столкновение является бесконечно малым), но вы все равно получите широкую дисперсию значений, которые, по-видимому, достаточно хороши для вашей проблемы.
Функции SQL, полученные из других ответов:
Для bigint:
create function h_bigint(text) returns bigint as $$
select ('x'||substr(md5($1),1,16))::bit(64)::bigint;
$$ language sql;
Для int:
create function h_int(text) returns int as $$
select ('x'||substr(md5($1),1,8))::bit(32)::int;
$$ language sql;
Вы можете создать хеш-значение md5 без проблем:
select md5('hello, world');
Возвращает строку с шестнадцатеричным номером.
К сожалению, нет встроенной функции для преобразования hex в integer, но, как вы это делаете в PL/pgSQL, это может помочь:
Должно быть, это целое число? Модуль pg_crypto предоставляет ряд стандартных хэш-функций (md5, sha1 и т.д.). Они все возвращают bytea. Я полагаю, вы могли бы выбросить несколько бит и преобразовать bytea в integer.
bigint слишком мал, чтобы хранить криптографический хеш. Наибольшие не-байтовые двоичные типы Pg поддерживают uuid. Вы можете перевести дайджест в uuid следующим образом:
select ('{'||encode( substring(digest('foobar','sha256') from 1 for 16), 'hex')||'}')::uuid;
uuid
--------------------------------------
c3ab8ff1-3720-e8ad-9047-dd39466b3c89