PostgreSQL: convierte una cadena hexadecimal de un número muy grande en un NUMÉRICO

Solución:

Esto es una especie de fuerza bruta y nada a prueba de balas:

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS numeric AS $$
DECLARE
  result  NUMERIC;
  i integer;
  len integer;
  hexchar varchar;
BEGIN

  result := 0;
  len := length(hexval);

  for i in 1..len loop
    hexchar := substr(hexval, len - i + 1, 1);
    result := result + round(16 ^ (i - 1)::dec * case
      when hexchar between '0' and '9' then cast (hexchar as int)
      when upper (hexchar) between 'A' and 'F' then ascii(upper(hexchar)) - 55
    end);
  end loop;

 RETURN result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

select hex_to_int('12AE34F');  -- returns 19587919

O, si tiene PL / Perl instalado, puede dejar que haga el trabajo pesado:

CREATE OR REPLACE FUNCTION hex_to_int_perl(varchar)
  RETURNS numeric AS
$BODY$
  my ($hex) = @_;
  return sprintf "%d", hex($hex);
$BODY$
  LANGUAGE plperl VOLATILE
  COST 100;

select hex_to_int_perl('12AE34F');  -- returns 19587919

No creo que el que no sea de Perl funcione con números negativos, y estoy bastante seguro de que ambos te darán malos resultados si pones un valor que no sea hexadecimal, pero esos serían escenarios bastante fáciles de atrapar y manejar, dependiendo sobre lo que desea que haga la función.

Mi caso de uso para esto fue convertir valores hash SHA-1 hexadecimales en números enteros. Sin prestar atención a la precisión numérica en toda la función, este caso de uso revela fácilmente las deficiencias; pero ciertamente los casos de uso con “números muy grandes” aún mayores son fáciles de identificar.

Dados algunos hash SHA-1, la primera de las soluciones de respuesta aceptadas arrojó lo siguiente:

SELECT hex_to_int('356e90d2a2d414ba8757ec2ab91f2f19c481d4c3');
-- returns 305042208670409000000000000000000000000000000000

SELECT hex_to_int('aaa9f7193cc8efe7e98145b0f8d9ae5f1712c25b');
-- returns 974318782301086000000000000000000000000000000000

Por supuesto, cada resultado que se rellena a la derecha con tantos ceros es un signo de precisión inadecuada para el caso de uso.

Aquí es donde terminé para obtener la precisión que necesitaba:

CREATE OR REPLACE FUNCTION hex_to_int(hexVal varchar) RETURNS numeric(1000) AS $$
DECLARE
    intVal numeric(1000) := 0;
    hexLength integer;
    i integer;
    hexDigit varchar;
BEGIN
    hexLength := length(hexVal);

    FOR i IN 1..hexLength
    LOOP
        hexDigit := substr(hexVal, hexLength - i + 1, 1);
        intVal :=
            intVal +
            CASE
                WHEN hexDigit BETWEEN '0' AND '9' THEN CAST(hexDigit AS numeric(1000))
                WHEN upper(hexDigit) BETWEEN 'A' AND 'F' THEN CAST(ascii(upper(hexDigit)) - 55 AS numeric(1000))
            END *
            CAST(16 AS numeric(1000)) ^ CAST(i - 1 AS numeric(1000));
    END LOOP;

    RETURN intVal;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;


SELECT hex_to_int('356e90d2a2d414ba8757ec2ab91f2f19c481d4c3');
-- returns 305042208670409212880202819376501392142011323587

SELECT hex_to_int('aaa9f7193cc8efe7e98145b0f8d9ae5f1712c25b');
-- returns 974318782301085717223606317572453925850501530203

Otra función de conversión más. La idea es reducir el número de pasos en el ciclo y, por tanto, el número de operaciones aritméticas.

create or replace function hex_to_numeric(str text)
returns numeric language plpgsql immutable strict as $$
declare
    i int;
    n int = length(str)/ 8;
    res dec = 0;
begin
    str := lpad($1, (n+ 1)* 8, '0');
    for i in 0..n loop
        if i > 0 then 
            res:= res * 4294967296; 
        end if;
        res:= res + concat('x', substr(str, i* 8+ 1, 8))::bit(32)::bigint::dec;
    end loop;
    return res;
end $$;

Algunas pruebas:

select hex, hex_to_numeric(hex) 
from   (
   values ('ff'::text),
        ('7fffffff'),
        ('80000000'),
        ('deadbeef'),
        ('7fffffffffffffff'),
        ('8000000000000000'),
        ('ffffffffffffffff'),
        ('ffffffffffffffff123'),
        ('4540a085e7334d6494dd6a7378c579f6')
   ) t(hex);

               hex                |             hex_to_numeric             
----------------------------------+----------------------------------------
 ff                               |                                    255
 7fffffff                         |                             2147483647
 80000000                         |                             2147483648
 deadbeef                         |                             3735928559
 7fffffffffffffff                 |                    9223372036854775807
 8000000000000000                 |                    9223372036854775808
 ffffffffffffffff                 |                   18446744073709551615
 ffffffffffffffff123              |                75557863725914323415331
 4540a085e7334d6494dd6a7378c579f6 | 92052294502540680826164862654582454774
(9 rows)

---

---

Utiliza Nuestro Buscador