pl_pgsql_random_text_generator.sql

create schema if not exists random;

create temp table word_copy(word TEXT);
COPY word_copy from '/usr/share/dict/ngerman' ENCODING 'UTF8';

create table random.words(id BIGSERIAL PRIMARY KEY, word TEXT);
insert into random.words(word) SELECT word from word_copy;


create or replace function random.random_ids(
    id_count INTEGER, max_num BIGINT, OUT id BIGINT) RETURNS SETOF BIGINT AS $$
DECLARE
BEGIN
    while (id_count > 0) LOOP
        SELECT INTO id ceil(random() * max_num)::BIGINT;
        RETURN NEXT;
        id_count := id_count - 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;


create or replace function random.random_text_generator(
    words INTEGER, sentences INTEGER, paragraphs INTEGER, OUT randomtext TEXT
) AS $$
DECLARE
    t_todo INTEGER;
    t_text TEXT;
    t_sentence_begin TEXT;
    t_count BIGINT;
    t_max BIGINT;
BEGIN
    randomtext := '';

    IF (paragraphs > 0) THEN
        t_todo := paragraphs;
        WHILE (t_todo > 0) LOOP
            t_todo := t_todo - 1;
            randomtext := randomtext || random.random_text_generator(
                words,
                ceil(random() * sentences)::INTEGER,
                0
            );
            IF (t_todo > 0) THEN
                randomtext := randomtext || E'\n\n';
            END IF;
        END LOOP;
    ELSIF (sentences > 0) THEN
        t_todo := sentences;
        WHILE (t_todo > 0) LOOP
            t_todo := t_todo - 1;
            t_text := random.random_text_generator(
                ceil(random() * words)::INTEGER,
                0,
                0
            );

            t_sentence_begin := upper(substring(t_text from 1 for 1));
            t_text := regexp_replace(t_text, '^.', t_sentence_begin);

            randomtext := randomtext || t_text || '.';

            IF (t_todo > 0) THEN
                randomtext := randomtext || ' ';
            END IF;
        END LOOP;
    ELSE
        SELECT INTO t_max max(id) FROM random.words;
        t_todo := words;
        IF (t_todo > 0) THEN
            SELECT
                INTO t_text
                string_agg(w.word, ' ')
            FROM
                (
                    select
                        word as word
                    from
                        random.words
                    WHERE 
                        id in (
                            SELECT id FROM random.random_ids(t_todo, t_max)
                        )
                ) AS w;

            randomtext := randomtext || t_text;
        END IF;
    END IF;

END;
$$ LANGUAGE plpgsql VOLATILE;