PostgreSQL：将非常大的数字的十六进制字符串转换为NUMERIC

我正在尝试转换非常大的数字的十六进制字符串 到数字列

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS NUMERIC AS $$
DECLARE
   result  NUMERIC;
BEGIN
 EXECUTE 'SELECT x''' || hexval || '''::NUMERIC(40,0)' INTO result;
 RETURN result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

我正在尝试这样做：

select hex_to_int(tx.value) from internal_transaction tx

我得到的错误是：

[42846] ERROR: cannot cast type bit to numeric Where: PL/pgSQL function hex_to_int(character varying) line 5 at EXECUTE statement

还有一个转换函数。这个想法是减少循环中的步骤数，从而减少算术运算的数量。

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
        res:= res * 4294967296; -- hex 100000000
        res:= res + concat('x', substr(str, i* 8+ 1, 8))::bit(32)::bigint::dec;
    end loop;
    return res;
end $$;

一些测试：

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)

该函数比其他答案中的 plpgsql 函数快 5-10 倍（取决于十六进制值的长度）。