Cuid v1 implementation for PostgreSQL

gistfile1.txt

-- ==========================================================================
--  Single script to create cuid() function and its dependencies
--  in PostgreSQL. Mimics the core logic of cuid (collision-resistant IDs).
-- ==========================================================================

-- Optional: Reduce NOTICE messages during script execution
-- SET client_min_messages = warning;

BEGIN;

-- --------------------------------------------------------------------------
--  Helper function: Convert bigint to base-N string (2 <= N <= 36)
-- --------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION to_base(n bigint, b int)
RETURNS text LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
    chars text := '0123456789abcdefghijklmnopqrstuvwxyz';
    res text := '';
    rem int;
BEGIN
    -- Basic validation
    IF n IS NULL OR b IS NULL OR b < 2 OR b > 36 THEN
        RETURN NULL;
    END IF;
    -- Handle zero separately
    IF n = 0 THEN
        RETURN '0';
    END IF;

    -- Convert to base N
    WHILE n > 0 LOOP
        rem := n % b; -- Modulo operation (bigint % int -> int)
        res := substr(chars, rem + 1, 1) || res; -- Prepend the character
        n := n / b; -- Integer division (bigint / int -> bigint)
    END LOOP;
    RETURN res;
END;
$$;

-- --------------------------------------------------------------------------
--  Helper function: Convert bigint to base-36 string
-- --------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION to_base36(n bigint)
RETURNS text LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT to_base(n, 36);
$$;

-- --------------------------------------------------------------------------
--  Sequence: Persistent counter for CUID generation
-- --------------------------------------------------------------------------
-- Use IF NOT EXISTS to allow the script to be run multiple times safely.
CREATE SEQUENCE IF NOT EXISTS cuid_counter;

-- --------------------------------------------------------------------------
--  Main function: Generate CUID string
-- --------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION cuid()
RETURNS text LANGUAGE plpgsql VOLATILE AS $$
DECLARE
    -- Constants matching cuid spec
    c_base int := 36;
    c_block_size int := 4;
    c_discrete_values bigint := 1679616; -- 36^4

    -- Variables for CUID parts
    v_letter text := 'c'; -- Starts with 'c'
    v_timestamp_ms bigint;
    v_timestamp_str text;
    v_counter_val bigint;
    v_counter_str text;
    v_pid int;
    v_fp_val1 bigint;
    v_fp_val2 bigint;
    v_pid_divided bigint;
    v_fingerprint_str text;
    v_random_val1 bigint;
    v_random_val2 bigint;
    v_random_str text;

BEGIN
    -- 1. Timestamp: Milliseconds since epoch, base 36
    --    Use clock_timestamp() for higher resolution within transactions.
    --    Explicitly cast to bigint after floor.
    v_timestamp_ms := floor(extract(epoch from clock_timestamp()) * 1000)::bigint;
    v_timestamp_str := to_base36(v_timestamp_ms);

    -- 2. Counter: Get next sequence value, wrap around, convert to base 36, pad.
    --    Modulo ensures the counter stays within the discrete values range.
    v_counter_val := nextval('cuid_counter') % c_discrete_values;
    v_counter_str := lpad(to_base36(v_counter_val), c_block_size, '0');

    -- 3. Fingerprint: Use backend PID as a substitute for hostname/node PID.
    --    Split into two 2-character base-36 parts (total 4 chars).
    v_pid := pg_backend_pid(); -- Returns integer PID of the current backend
    v_fp_val1 := v_pid % 1296; -- First part (1296 = 36^2)
    v_pid_divided := v_pid / 1296; -- Integer division for the second part calculation
    v_fp_val2 := v_pid_divided % 1296; -- Second part
    v_fingerprint_str := lpad(to_base36(v_fp_val1), 2, '0') ||
                         lpad(to_base36(v_fp_val2), 2, '0');

    -- 4. Random Blocks: Two blocks of random data, base 36, padded.
    --    Explicitly cast to bigint after floor.
    v_random_val1 := floor(random() * c_discrete_values)::bigint;
    v_random_val2 := floor(random() * c_discrete_values)::bigint;
    v_random_str := lpad(to_base36(v_random_val1), c_block_size, '0') ||
                    lpad(to_base36(v_random_val2), c_block_size, '0');

    -- 5. Concatenate all parts
    RETURN v_letter || v_timestamp_str || v_counter_str || v_fingerprint_str || v_random_str;

END;
$$;

-- Optional: Confirmation message upon successful execution
-- RAISE NOTICE 'cuid() function and dependencies created/updated successfully.';

COMMIT;

-- ==========================================================================
--  End of script
-- ==========================================================================