PostgreSQL: convert hex string of a very large number to a NUMERIC

Question

I am trying to convert hex string of a very large number to a NUMERIC column

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;

There I am trying to do this:

select hex_to_int(tx.value) from internal_transaction tx

The error I get is :

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

Answer 1

This is sort of brute force and not at all bulletproof:

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

Or, if you have PL/Perl installed, you can let it do the heavy lifting:

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

I don't think the non-Perl one works with negative numbers, and I'm quite sure both will give you bad results if you put in a non-hex value, but those would be easy enough scenarios to trap and handle, depending on what you want the function to do.