Thanks for helping me out everyone. I ended up simply using the numeric type (I didn't realize it could support such large numbers) and writing the hex-to-numeric conversion functions in my application code.
On 11 April 2014 12:27, Leon Smith <leon.p.sm...@gmail.com> wrote: > 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 >> > > -- - Oli Olivier Lalonde http://www.syskall.com <-- connect with me! Freelance web and Node.js engineer Skype: o-lalonde
