Re: Adding unsigned 256 bit integers - Mailing list pgsql-hackers

<div dir="ltr">pgmp is also worth mentioning here,   and it's likely to be more efficient than the numeric type or
somethingyou hack up yourself:<br /><br /><a
href="http://pgmp.projects.pgfoundry.org/">http://pgmp.projects.pgfoundry.org/</a><br/><br />Best,<br />Leon</div><div
class="gmail_extra"><br/><br /><div class="gmail_quote">On Thu, Apr 10, 2014 at 10:11 AM, <a
href="mailto:ktm@rice.edu">ktm@rice.edu</a><span dir="ltr"><<a href="mailto:ktm@rice.edu"
target="_blank">ktm@rice.edu</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"><div class="HOEnZb"><div class="h5">On Thu, Apr 10, 2014 at 09:13:47PM
+0800,Olivier Lalonde wrote:<br /> > I was wondering if there would be any way to do the following in PostgreSQL:<br
/>><br /> >     UPDATE cryptotable SET work = work + 'some big hexadecimal number'<br /> ><br /> > where
workis an unsigned 256 bit integer. Right now my column is a<br /> > character varying(64) column (hexadecimal
representationof the number) but<br /> > I would be happy to switch to another data type if it lets me do the<br />
>operation above.<br /> ><br /> > If it's not possible with vanilla PostgreSQL, are there extensions that<br
/>> could help me?<br /> ><br /> > --<br /> > - Oli<br /> ><br /> > Olivier Lalonde<br /> > <a
href="http://www.syskall.com"target="_blank">http://www.syskall.com</a> <-- connect with me!<br /> ><br /><br
/></div></div>HiOlivier,<br /><br /> Here are some sample pl/pgsql helper functions that I have written for<br /> other
purposes.They use integers but can be adapted to use numeric.<br /><br /> Regards,<br /> Ken<br />
---------------------------<br/> CREATE OR REPLACE FUNCTION hex2dec(t text) RETURNS integer AS $$<br /> DECLARE<br />  
rRECORD;<br /> BEGIN<br />   FOR r IN EXECUTE 'SELECT x'''||t||'''::integer AS hex' LOOP<br />     RETURN r.hex;<br />
 END LOOP;<br /> END<br /> $$ LANGUAGE plpgsql IMMUTABLE STRICT;<br /> ---------------------------<br /><br />
---------------------------<br/> CREATE OR REPLACE FUNCTION bytea2int (<br />   in_string BYTEA<br /> ) RETURNS INTEGER
AS$$<br /><br /> DECLARE<br /><br />   b1 INTEGER := 0;<br />   b2 INTEGER := 0;<br />   b3 INTEGER := 0;<br />   b4
INTEGER:= 0;<br />   out_int INTEGER := 0;<br /><br /> BEGIN<br /><br />   CASE OCTET_LENGTH(in_string)<br />     WHEN
1THEN<br />       b4 := get_byte(in_string, 0);<br />     WHEN 2 THEN<br />       b3 := get_byte(in_string, 0);<br />  
   b4 := get_byte(in_string, 1);<br />     WHEN 3 THEN<br />       b2 := get_byte(in_string, 0);<br />       b3 :=
get_byte(in_string,1);<br />       b4 := get_byte(in_string, 2);<br />     WHEN 4 THEN<br />       b1 :=
get_byte(in_string,0);<br />       b2 := get_byte(in_string, 1);<br />       b3 := get_byte(in_string, 2);<br />      
b4:= get_byte(in_string, 3);<br />   END CASE;<br /><br />   out_int := (b1 << 24) + (b2 << 16) + (b3
<<8) + b4;<br /><br />   RETURN(out_int);<br /> END;<br /> $$ LANGUAGE plpgsql IMMUTABLE;<br />
---------------------------<br/><div class="HOEnZb"><div class="h5"><br /><br /> --<br /> Sent via pgsql-hackers
mailinglist (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br /> To make changes to
yoursubscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></div></div></blockquote></div><br /></div>