Created
February 18, 2022 17:06
-
-
Save equalent/4da0403fd866c4b922adb8e14350cb5a to your computer and use it in GitHub Desktop.
PostgreSQL PL/pgSQL NUMERIC to BIT VARYING
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
-- source: https://stackoverflow.com/a/50119025 | |
create function numeric_to_bit(numeric) returns bit varying | |
language plpgsql | |
as | |
$$ | |
DECLARE | |
num ALIAS FOR $1; | |
-- 1 + largest positive BIGINT -- | |
max_bigint NUMERIC := '9223372036854775808' :: NUMERIC(19, 0); | |
result BIT VARYING; | |
BEGIN | |
WITH | |
chunks (exponent, chunk) AS ( | |
SELECT | |
exponent, | |
floor((num / (max_bigint ^ exponent) :: NUMERIC(300, 20)) % max_bigint) :: BIGINT | |
FROM generate_series(0, 5) exponent | |
) | |
SELECT bit_or(chunk :: BIT(300) :: BIT VARYING << (63 * (exponent))) :: BIT VARYING | |
FROM chunks INTO result; | |
RETURN result; | |
END; | |
$$; | |
alter function numeric_to_bit(numeric) owner to postgres; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment