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;
---------------------------