Re: [HACKERS] Adding unsigned 256 bit integers

2014-04-14 Thread Olivier Lalonde
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


Re: [HACKERS] Adding unsigned 256 bit integers

2014-04-10 Thread Andrew Dunstan


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


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


Re: [HACKERS] Adding unsigned 256 bit integers

2014-04-10 Thread Craig Ringer
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


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


Re: [HACKERS] Adding unsigned 256 bit integers

2014-04-10 Thread k...@rice.edu
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


Re: [HACKERS] Adding unsigned 256 bit integers

2014-04-10 Thread Leon Smith
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