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;