Recently, I had to create a PostgreSQL equivalent of the Java hashCode()
function. In order to do this, I needed a way to make sure that numbers that exceed the INTEGER representation (ranging from -2^31 to 2^31-1) will be wrapped. In JavaScript, this is as simple as using the logical shift left operator on the number. On the other hand, I was unable to find a simple way to do this in PostgreSQL. Therefore, I started using JavaScript to figure out a way to create a function which would allow me to simulate what is natively available in JavaScript. I came up with the following JavaScript function:
function wrapNumber(num, bits, unsigned) { var most = Math.pow(2, bits), ret = num + (unsigned ? 0 : most / 2); return (ret<0 ? Math.ceil(-ret/most) : -Math.floor(ret/most)) * most + num; }
After testing the above function, I created the following PostgreSQL function:
CREATE OR REPLACE FUNCTION wrap_signed_bigint( IN in_number BIGINT, IN in_bits SMALLINT ) RETURNS BIGINT AS $$ DECLARE most BIGINT; ret BIGINT; BEGIN most := 2 ^ in_bits; ret := in_number + most / 2; IF ret >= 0 OR (ret / most) * most = ret THEN ret := -ret / most; ELSE ret := -ret / most + 1; END IF; RETURN ret * most + in_number; END; $$ LANGUAGE plpgsql;
Now if a number outside of the 32-bit range is passed in, a corresponding number that is in that range will be returned:
SELECT num, msg, wrap_signed_bigint(CAST(num AS BIGINT), CAST(32 AS SMALLINT)) FROM ( SELECT 'lowest negative number' AS msg, 2^31 AS num UNION SELECT 'back to zero' AS msg, 2^32 AS num UNION SELECT 'highest positive number' AS msg, -2^31 - 1 AS num ) t
By the way, if you want a signed version of the wrap function, you may use this code:
CREATE OR REPLACE FUNCTION wrap_unsigned_bigint( IN in_number BIGINT, IN in_bits SMALLINT ) RETURNS BIGINT AS $$ DECLARE most BIGINT; ret BIGINT; BEGIN most := 2 ^ in_bits; ret := in_number; IF ret >= 0 OR (ret / most) * most = ret THEN ret := -ret / most; ELSE ret := -ret / most + 1; END IF; RETURN ret * most + in_number; END; $$ LANGUAGE plpgsql;