Generate a UUIDv7 with a PL/pgSQL function

generate_uuidv7.sql

-- IETF Draft Spec: https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-01.html

CREATE SEQUENCE uuidv7_seq MAXVALUE 4095; -- A 12-bit sequence

CREATE OR REPLACE FUNCTION generate_uuidv7()
RETURNS uuid AS $$
DECLARE
  unixts bigint;
  msec bigint;
  seq bigint;
  rand bigint;
  uuid_hex varchar;
BEGIN
  -- Get current UNIX epoch in milliseconds
  unixts := (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint;

  -- Extract milliseconds
  msec := unixts % 1000; -- Milliseconds

  -- Get next value from the sequence for the "motonic clock sequence counter" value
  seq := NEXTVAL('uuidv7_seq');

  -- Generate a random 62-bit number
  rand := (RANDOM() * 4611686018427387903)::bigint; -- 62-bit random number

  -- Construct the UUID
  uuid_hex := LPAD(TO_HEX(((unixts << 28) + (msec << 16) + (7 << 12) + seq)), 16, '0') ||
              LPAD(TO_HEX((2 << 62) + rand), 16, '0');

  -- Return the UUID
  RETURN uuid_hex::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT generate_uuidv7();