Обсуждение: Adding unsigned 256 bit integers

On 04/10/2014 09:13 AM, 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?
>
>


The numeric type allows numbers with huge numbers of digits. I've used 
it to calculate fibonacci numbers thousands of digits long.

cheers

andrew

On 04/10/2014 09:13 PM, 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'

For readers finding this in the archives, this question also appears here:

http://dba.stackexchange.com/questions/62934/adding-unsigned-256-bit-integers-in-postgresql

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

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

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