Created
April 12, 2018 15:23
-
-
Save thepaul/d747863d3e7932740e129ba3343a91b1 to your computer and use it in GitHub Desktop.
postgresql PL/PGSQL functions for encoding 64-bit numbers as 8-byte strings and vice versa
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- transform 8 bytes into an int64 | |
CREATE FUNCTION bytes_to_int64(s BYTEA, OUT result INT8) | |
AS $$ | |
BEGIN | |
SELECT bit_or(get_byte(s, ind)::INT8 << ((7 - ind) * 8)) | |
INTO result | |
FROM generate_series(0, 7) AS ind; | |
END; | |
$$ LANGUAGE 'plpgsql'; | |
-- transform an int64 into 8 bytes | |
CREATE FUNCTION int64_to_bytes(i INT8, OUT result BYTEA) | |
AS $$ | |
BEGIN | |
SELECT string_agg(set_byte(' ', 0, ((i >> boffset) & 255)::INTEGER), ''::BYTEA) | |
INTO result | |
FROM generate_series(56, 0, -8) AS boffset; | |
END; | |
$$ LANGUAGE 'plpgsql'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment