Sequential UUIDs for PostgreSQL

sequential_uuid.sql

-- Sequential UUIDs in PostgreSQL
-- 
-- Afer ages of trying to figure this out (because apparently no one
-- needs sequential UUIDs), I finally figured out how to do this.
--
-- They aren't exactly UUIDs, according to the standard, but they are 
-- random 128 bit sequences that fit into the Postgres UUID type.
--
-- It's not good code and probably not very fast, but it's a good start.
--
-- The first 48 bits are the current Unix timestamp in milliseconds.
--
-- Hopefully this saves someone time.
--
-- (c) 2016 Aapeli Vuorinen. 
-- www.aapelivuorinen.com 
-- Released into the public domain.



-- Needed for gen_random_bytes()
CREATE EXTENSION "pgcrypto";

CREATE OR REPLACE FUNCTION aapeli_uuid(OUT result uuid) AS $$
DECLARE
  now_millis bigint;
BEGIN
  SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
  result := (lpad(to_hex((now_millis::bit(48)
          || get_byte(gen_random_bytes(10),0)::bit(8)
          || get_byte(gen_random_bytes(10),0)::bit(8))::bit(64)::bigint), 16, '0')
          || lpad(to_hex(
            (get_byte(gen_random_bytes(10),0)::bit(8)
          || get_byte(gen_random_bytes(10),0)::bit(8)
          || get_byte(gen_random_bytes(10),0)::bit(8)
          || get_byte(gen_random_bytes(10),0)::bit(8)
          || get_byte(gen_random_bytes(10),0)::bit(8)
          || get_byte(gen_random_bytes(10),0)::bit(8)
          || get_byte(gen_random_bytes(10),0)::bit(8)
          || get_byte(gen_random_bytes(10),0)::bit(8))::bit(64)::bigint), 16, '0'))::text::uuid;
END;
$$ LANGUAGE PLPGSQL;