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, k...@rice.edu <k...@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 >
