Re: Adding unsigned 256 bit integers

On Thu, Apr 10, 2014 at 09:13:47PM +0800, Olivier Lalonde wrote:
> I was wondering if there would be any way to do the following in PostgreSQL:
> 
>     UPDATE cryptotable SET work = work + 'some big hexadecimal number'
> 
> where work is an unsigned 256 bit integer. Right now my column is a
> character varying(64) column (hexadecimal representation of the number) but
> I would be happy to switch to another data type if it lets me do the
> operation above.
> 
> If it's not possible with vanilla PostgreSQL, are there extensions that
> could help me?
> 
> -- 
> - Oli
> 
> Olivier Lalonde
> http://www.syskall.com <-- connect with me!
> 

Hi Olivier,

Here are some sample pl/pgsql helper functions that I have written for
other purposes. They use integers but can be adapted to use numeric.

Regards,
Ken
---------------------------
CREATE OR REPLACE FUNCTION hex2dec(t text) RETURNS integer AS $$
DECLARE r RECORD;
BEGIN FOR r IN EXECUTE 'SELECT x'''||t||'''::integer AS hex' LOOP   RETURN r.hex; END LOOP;
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
---------------------------

---------------------------
CREATE OR REPLACE FUNCTION bytea2int ( in_string BYTEA
) RETURNS INTEGER AS $$

DECLARE
 b1 INTEGER := 0; b2 INTEGER := 0; b3 INTEGER := 0; b4 INTEGER := 0; out_int INTEGER := 0;

BEGIN
 CASE OCTET_LENGTH(in_string)   WHEN 1 THEN     b4 := get_byte(in_string, 0);   WHEN 2 THEN     b3 :=
get_byte(in_string,0);     b4 := get_byte(in_string, 1);   WHEN 3 THEN     b2 := get_byte(in_string, 0);     b3 :=
get_byte(in_string,1);     b4 := get_byte(in_string, 2);   WHEN 4 THEN     b1 := get_byte(in_string, 0);     b2 :=
get_byte(in_string,1);     b3 := get_byte(in_string, 2);     b4 := get_byte(in_string, 3); END CASE;
 
 out_int := (b1 << 24) + (b2 << 16) + (b3 << 8) + b4;
 RETURN(out_int);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
---------------------------