Created
November 13, 2018 10:32
-
-
Save boxofrad/4f638a813588b6569ce5b2c4bc071f10 to your computer and use it in GitHub Desktop.
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
CREATE EXTENSION IF NOT EXISTS pgcrypto; | |
-- Based roughly on: https://github.com/oklog/ulid | |
CREATE FUNCTION generate_ulid() | |
RETURNS TEXT | |
AS $$ | |
DECLARE | |
-- Crockford's Base32 | |
encoding BYTEA = '0123456789ABCDEFGHJKMNPQRSTVWXYZ'; | |
timestamp BYTEA = E'\\000\\000\\000\\000\\000\\000'; | |
output TEXT = ''; | |
unix_time BIGINT; | |
ulid BYTEA; | |
BEGIN | |
-- 6 timestamp bytes | |
unix_time = (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT; | |
timestamp = SET_BYTE(timestamp, 0, (unix_time >> 40)::BIT(8)::INTEGER); | |
timestamp = SET_BYTE(timestamp, 1, (unix_time >> 32)::BIT(8)::INTEGER); | |
timestamp = SET_BYTE(timestamp, 2, (unix_time >> 24)::BIT(8)::INTEGER); | |
timestamp = SET_BYTE(timestamp, 3, (unix_time >> 16)::BIT(8)::INTEGER); | |
timestamp = SET_BYTE(timestamp, 4, (unix_time >> 8)::BIT(8)::INTEGER); | |
timestamp = SET_BYTE(timestamp, 5, unix_time::BIT(8)::INTEGER); | |
-- 10 entropy bytes | |
ulid = timestamp || gen_random_bytes(10); | |
-- Encode the timestamp | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 0) & 224) >> 5)); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 0) & 31))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 1) & 248) >> 3)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 1) & 7) << 2) | ((GET_BYTE(ulid, 2) & 192) >> 6))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 2) & 62) >> 1)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 2) & 1) << 4) | ((GET_BYTE(ulid, 3) & 240) >> 4))); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 3) & 15) << 1) | ((GET_BYTE(ulid, 4) & 128) >> 7))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 4) & 124) >> 2)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 4) & 3) << 3) | ((GET_BYTE(ulid, 5) & 224) >> 5))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 5) & 31))); | |
-- Encode the entropy | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 6) & 248) >> 3)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 6) & 7) << 2) | ((GET_BYTE(ulid, 7) & 192) >> 6))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 7) & 62) >> 1)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 7) & 1) << 4) | ((GET_BYTE(ulid, 8) & 240) >> 4))); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 8) & 15) << 1) | ((GET_BYTE(ulid, 9) & 128) >> 7))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 9) & 124) >> 2)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 9) & 3) << 3) | ((GET_BYTE(ulid, 10) & 224) >> 5))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 10) & 31))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 11) & 248) >> 3)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 11) & 7) << 2) | ((GET_BYTE(ulid, 12) & 192) >> 6))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 12) & 62) >> 1)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 12) & 1) << 4) | ((GET_BYTE(ulid, 13) & 240) >> 4))); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 13) & 15) << 1) | ((GET_BYTE(ulid, 14) & 128) >> 7))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 14) & 124) >> 2)); | |
output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 14) & 3) << 3) | ((GET_BYTE(ulid, 15) & 224) >> 5))); | |
output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 15) & 31))); | |
RETURN output; | |
END | |
$$ | |
LANGUAGE plpgsql | |
VOLATILE; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment