generate_item_json.sql

CREATE OR REPLACE FUNCTION generate_item_json(target_description_length INT)
RETURNS JSONB AS $$
DECLARE
    -- 1. Expanded Lorem Ipsum pool
    lorem_pool TEXT[] := ARRAY[
        'lorem', 'ipsum', 'dolor', 'sit', 'amet', 'consectetur', 'adipiscing', 'elit', 
        'sed', 'do', 'eiusmod', 'tempor', 'incididunt', 'ut', 'labore', 'et', 'dolore', 
        'magna', 'aliqua', 'ut', 'enim', 'ad', 'minim', 'veniam', 'quis', 'nostrud', 
        'exercitation', 'ullamco', 'laboris', 'nisi', 'ut', 'aliquip', 'ex', 'ea', 
        'commodo', 'consequat', 'duis', 'aute', 'irure', 'dolor', 'in', 'reprehenderit', 
        'in', 'voluptate', 'velit', 'esse', 'cillum', 'dolore', 'eu', 'fugiat', 'nulla', 
        'pariatur', 'excepteur', 'sint', 'occaecat', 'cupidatat', 'non', 'proident', 
        'sunt', 'in', 'culpa', 'qui', 'officia', 'deserunt', 'mollit', 'anim', 'id', 'est', 'laborum'
    ];
    v_description TEXT := '';
    v_name TEXT;
    v_word TEXT;
    v_pool_size INT := array_length(lorem_pool, 1);
    v_random_pk BIGINT;
BEGIN
    -- 2. Generate random PK from a large pool (1 to 1 billion)
    v_random_pk := floor(random() * 1000000000)::BIGINT;

    -- 3. Select a random word for the item_name
    v_name := lorem_pool[floor(random() * v_pool_size + 1)];

    -- 4. Build random selection of words for description
    WHILE length(v_description) < target_description_length LOOP
        v_word := lorem_pool[floor(random() * v_pool_size + 1)];
        
        IF length(v_description || ' ' || v_word) > target_description_length THEN
            EXIT;
        END IF;

        IF v_description = '' THEN
            v_description := v_word;
        ELSE
            v_description := v_description || ' ' || v_word;
        END IF;
    END LOOP;

    -- 5. Return JSONB
    RETURN jsonb_build_object(
        'item_pk', v_random_pk,
        'item_name', v_name,
        'item_price', round((random() * 100)::numeric, 2),
        'item_description', v_description
    );
END;
$$ LANGUAGE plpgsql;