Pure SQL Function for Generating UUIDv7 on PostgreSQL

pure_pgsql_uuid7.sql

CREATE OR REPLACE FUNCTION uuid7_sql() RETURNS uuid AS $$
       SELECT (FORMAT('%s-%s-%0s-%s-%s', 
       lpad(to_hex(trunc(EXTRACT(EPOCH FROM statement_timestamp()) * 1000)::bigint >> 16), 8, '0'),
       lpad(to_hex(trunc(EXTRACT(EPOCH FROM statement_timestamp()) * 1000)::bigint & 65535), 4, '0'),
       lpad(to_hex((trunc(random() * 2^12) + 28672)::bigint), 4, '0'), -- 28672 = 0x7000
       lpad(to_hex((trunc(random() * 2^14) + 32768)::bigint), 4, '0'), -- 32768 = 0x8000
       lpad(to_hex(trunc(random() * 2^48)::bigint), 12, '0')))::uuid;
$$ LANGUAGE SQL;

select uuid7_sql() -- 019450fe-7b0f-7ccc-8564-ad3ccc8234e0

I'm not sure but it seems this function generates not lexicographically ordered values, could you check it?

How I tested it: see script below.

1. If I use uuid7_sql() function, I get invalid rows (where next value is less then current).

2. If I use uuid7() function from here: https://gist.github.com/fabiolimace/515a0440e3e40efeb234e12644a6a346#file-uuidv7-sql I get no invalid values.

You can comment/uncomment functions to get and compare results.
I've tested this on PostgreSQL 14.16 on Windows 10.

WITH RECURSIVE uuid_generator AS (
    SELECT
        1 AS row_num,
        uuid7_sql()
        --uuid7()
        AS current_uuid,
        NULL::uuid AS prev_uuid,
        FALSE AS should_break

    UNION ALL

    SELECT
        row_num + 1,
        CASE WHEN should_break THEN current_uuid ELSE
            uuid7_sql()
            --uuid7()
        END,
        current_uuid,
        should_break OR (current_uuid >
            uuid7_sql()
            --uuid7()
        )
    FROM uuid_generator
    WHERE row_num < 10000  -- Set your desired number of UUIDs here
)
SELECT
    row_num,
    current_uuid,
    prev_uuid,
    current_uuid < prev_uuid AS is_less_than_prev
FROM uuid_generator
WHERE
    (prev_uuid IS NOT NULL AND current_uuid < prev_uuid)
   OR row_num = (SELECT MIN(row_num) FROM uuid_generator WHERE prev_uuid IS NOT NULL AND current_uuid < prev_uuid) - 1
ORDER BY row_num
;

Another script to check function, and this script results the same as in previous script:

WITH RECURSIVE uuid_generator AS (
    SELECT
        uuid7_sql()
        --uuid7()
        AS current_uuid,
        1 AS iteration
    UNION ALL
    SELECT
        uuid7_sql()
        --uuid7()
        ,
        iteration + 1
    FROM uuid_generator
    WHERE iteration < 1000000 -- Replace value with the desired number of UUIDs to generate
),
               uuid_with_previous AS (
                   SELECT
                       current_uuid,
                       LAG(current_uuid) OVER (ORDER BY iteration) AS previous_uuid,
                       iteration
                   FROM uuid_generator
               )
SELECT
    current_uuid,
    previous_uuid,
    iteration
FROM uuid_with_previous
WHERE current_uuid < previous_uuid
LIMIT 1;