I couldn't find a good answer to the underlying question of converting a very large hexadecimal number to a numeric type, so even though it's several years late, here's my alternative approach:
CREATE FUNCTION hex2numeric(hex TEXT)
RETURNS NUMERIC AS $$
DECLARE
nibble RECORD;
decimal_digit NUMERIC;
result NUMERIC := 0;
BEGIN
-- Loop through each hexadecimal digit, starting from the right
FOR nibble IN (SELECT (n - 1) AS position, SUBSTRING(REVERSE(hex) FROM n FOR 1) AS digit FROM generate_series(1, length( hex ), 1) n) LOOP
-- get the decimal representation of the hex digit
decimal_digit := ('x' || nibble.digit)::BIT(4)::INT::NUMERIC;
-- multiply the digit by the nth power of 16 and add to the result
result := result + (decimal_digit * (16::NUMERIC^nibble.position::NUMERIC));
END LOOP;
RETURN result;
END
$$ LANGUAGE plpgsql IMMUTABLE;
Usage (e.g. with a 512-bit hex number):
psql=# select hex2numeric('87783308B813E3B4FB3271534D844EE8FDBDF302273B3E3D5CB3BF6E0117F45DBAFE1AD7D30FA91CE7F0D33C7E877CA544D1770E7AD4F37691426E89806CB48B')::NUMERIC(156,0);
hex2numeric
------------------------------------------------------------------------------------------------------------------------------------------------------------
7095114927382653019461910892845641210567311772867384729122528908252679853572077146666940749408552370045395041242349820090839313239920717104090735323165835
(1 row)
Given that it's doing it digit-by-digit it's probably not very performant, but it gets the job done for my use case.
This is an extension of the method described here for converting hex digits to decimals and the algorithm for converting and summing hex digits as described here.
numeric
type, but performance of unpacking hex into anumeric
in PL/PgSQL is likely to be absolutely horrifying. It might be worth writing a small C extension function to decode the hex into anumeric
. – Craig Ringer Apr 10 '14 at 13:35