Re: Adding unsigned 256 bit integers

pgmp is also worth mentioning here,   and it's likely to be more efficient than the numeric type or something you hack up yourself:

http://pgmp.projects.pgfoundry.org/

Best,
Leon

On Thu, Apr 10, 2014 at 10:11 AM, ktm@rice.edu <ktm@rice.edu> wrote:

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers