Polyfill for Postgresql 14+ with shim_ prefix to avoid colision with native functions.

uuidv7_polyfill.sql

-- Polyfill for Postgresql 14+ with shim_ prefix to avoid colision with native functions.
-- Based on official spec http://postgresql.org/docs/current/functions-uuid.html
-- and RFC 9562: https://datatracker.ietf.org/doc/html/rfc9562
-- Author: Leonan Carvalho <@leonancarvalho>
DO $$
DECLARE
    v_pg_version integer;
BEGIN
SELECT current_setting('server_version_num')::integer INTO v_pg_version;
    -- Create polyfill for pg server version below
    IF v_pg_version < 180000 THEN
        RAISE NOTICE 'PostgreSQL version % detected.', v_pg_version;
        RAISE NOTICE 'Installing UUIDv7 (RFC 9562)...';
        -------------------------------------------------------------------------
        -- Shim for uuidv7  RFC 9562 https://www.postgresql.org/docs/current/functions-uuid.html#FUNCTIONS-UUID
        -------------------------------------------------------------------------
        CREATE OR REPLACE FUNCTION public.shim_uuidv7(p_interval interval DEFAULT NULL )
            RETURNS uuid  AS $func$
        DECLARE
            v_time timestamptz;
            v_ms bigint;
            v_us_part bigint;
            v_hex text;
            v_rand_hex text;
            v_sub_ms_hex text;
        BEGIN
            -- 1. Extract mutable clock ts
            v_time := clock_timestamp();
            IF p_interval IS NOT NULL THEN
                v_time := v_time + p_interval;
            END IF;
            -- 2. Timestamp (48 bits - Milliseconds)
            v_ms := FLOOR(EXTRACT(EPOCH FROM v_time) * 1000)::bigint;
            v_hex := lpad(to_hex(v_ms), 12, '0');
            -- 3. High precision (12 bits - Fraction of Milliseconds)
            -- Extract microseconds (0-999) scale to 12 bits (0-4095)
            -- Rule: (micros % 1000) * 4095 / 1000
            v_us_part :=((EXTRACT(MICROSECONDS FROM v_time)::integer % 1000) * 4095) / 1000;
            v_sub_ms_hex := lpad(to_hex(v_us_part), 3, '0');
            -- Gen 3 chars hex (12 bits)
            -- 4. Random (62 bits remaining)
            -- Generate a UUID v4 and get last 16 characters (64 bits)
            v_rand_hex := encode(uuid_send(gen_random_uuid()), 'hex');
            -- 5. Build final Hexadecimal String
            -- Structure: [Time 12 chars] + [Ver '7'] + [Sub-ms 3 chars] + [Variant '8'] + [Rand 15 chars]
            v_hex := v_hex || -- 12 chars (Unix MS)
            '7' || -- 1 char   (Ver. 7)
            v_sub_ms_hex || -- 3 chars  (rand_a with sub-ms)
            '8' || -- 1 char   (Variant 1 RFC 9562 - https://www.rfc-editor.org/rfc/rfc9562.html#name-variant-field)
            substr(v_rand_hex, 18);
            -- 15 chars left from uuidv4
            -- Total: 12 + 1 + 3 + 1 + 15 = 32 hex characters = 128 bits.
            RETURN v_hex::uuid;
        END;
        $func$
		    LANGUAGE plpgsql VOLATILE LEAKPROOF PARALLEL SAFE;
        COMMENT ON FUNCTION public.shim_uuidv7(interval) IS 'Shim: Generate UUIDv7 RFC 9562 with same signature as PostgreSQL 18+. Remove and replace on upgrade';
        -------------------------------------------------------------------------
        -- 2. Function uuid_extract_timestamp (Polyfill RFC 9562)
        -------------------------------------------------------------------------
        CREATE OR REPLACE FUNCTION public.shim_uuid_extract_timestamp(p_uuid uuid)
            RETURNS timestamptz AS $func$
            SELECT
                to_timestamp ((
                    -- Extract first 6 bytes (48 bits) from UUID
                    -- Byte arithmetic is used to construct BIGINT.
                    get_byte(uuid_send(p_uuid ), 0 )::bit(8 ) || get_byte(uuid_send(p_uuid ), 1 )::bit(8 ) || get_byte(uuid_send(p_uuid ), 2 )::bit(8 ) || get_byte(uuid_send(p_uuid ), 3 )::bit(8 ) || get_byte(uuid_send(p_uuid ), 4 )::bit(8 ) || get_byte(uuid_send(p_uuid ), 5 )::bit(8 ) )::bit(48)::bigint::numeric / 1000.0
                );
            $func$
			  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT;
        COMMENT ON FUNCTION public.shim_uuid_extract_timestamp(uuid) IS 'Shim: Extracts timestamp from UUIDv7 (RFC 9562).';
        -------------------------------------------------------------------------
        -- 3. Function uuid_extract_version (Polyfill RFC 9562)
        -------------------------------------------------------------------------
        CREATE OR REPLACE FUNCTION public.shim_uuid_extract_version(p_uuid uuid )
            RETURNS integer
            IMMUTABLE PARALLEL SAFE STRICT AS $func$ -- The version nibble is in the 4 most significant bits of the 7th byte (index 6)
            SELECT (get_byte(uuid_send(p_uuid ), 6 ) >> 4 )::integer;
        $func$
		    LANGUAGE sql;
        COMMENT ON FUNCTION public.shim_uuid_extract_version(uuid) IS 'Shim: Returns the UUID version (e.g. 7 or 4).';
    ELSE
        RAISE NOTICE 'PostgreSQL version % detected (>= 18). Native functions should be used.', v_pg_version;
    END IF;
END
$$;