Function to convert non-negative integer represented as a byte array in big-endian order to numeric

numeric_from_bytes.sql

CREATE OR REPLACE FUNCTION numeric_from_bytes(bytea)
RETURNS numeric
LANGUAGE plpgsql
AS $$
declare
  bits bit varying;
  result numeric := 0;
  exponent numeric := 0;
  bit_pos integer;
begin
  execute 'SELECT x' || quote_literal(substr($1::text,3)) into bits;
  bit_pos := length(bits) + 1;
  exponent := 0;
  while bit_pos >= 56 loop
    bit_pos := bit_pos - 56;
    result := result + substring(bits from bit_pos for 56)::bigint::numeric * pow(2::numeric, exponent);
    exponent := exponent + 56;
  end loop;
  while bit_pos >= 8 loop
    bit_pos := bit_pos - 8;
    result := result + substring(bits from bit_pos for 8)::bigint::numeric * pow(2::numeric, exponent);
    exponent := exponent + 8;
  end loop;
  return trunc(result);
end;
$$;

/*
In a future PostgreSQL version, when/if Dean Rasheed's patch 0001-Add-non-decimal-integer-support-to-type-numeric.patch gets committed, this can be simplified:

CREATE OR REPLACE FUNCTION numeric_from_bytes(bytea)
RETURNS numeric
LANGUAGE sql
AS $$
SELECT ('0'||right($1::text,-1))::numeric
$$;

*/