Generate a version 7 UUID in PL/pgSQL as specified in the draft (https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-04.html)

uuidv7.sql

--- Generate a version 7 UUID in PL/pgSQL as specified in the draft (https://www.ietf.org/archive/id/draft-peabody-dispatch-new-uuid-format-04.html)

CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $$
DECLARE
    raw bytea;
    tsbits bit(48);
BEGIN
    raw := decode(replace(uuid_generate_v4()::text, '-', ''), 'hex');
    tsbits := (extract(epoch from clock_timestamp()) * 1000)::bigint::bit(48);
    --- set unix timestamp (big endian) in the upper 48 bits/6 bytes
    raw := set_byte(raw, 0, substring(tsbits from 1 for 8)::int);
    raw := set_byte(raw, 1, substring(tsbits from 9 for 8)::int);
    raw := set_byte(raw, 2, substring(tsbits from 17 for 8)::int);
    raw := set_byte(raw, 3, substring(tsbits from 25 for 8)::int);
    raw := set_byte(raw, 4, substring(tsbits from 33 for 8)::int);
    raw := set_byte(raw, 5, substring(tsbits from 41 for 8)::int);
    --- set uuid version
    raw := set_byte(raw, 6, (get_byte(raw, 6) & 15) | 112);
    RETURN encode(raw, 'hex')::uuid;
END;
$$ LANGUAGE plpgsql;

select uuid_generate_v7(); --- 018588d4-e602-7795-bf3d-2a3bad9161d9