CREATE OR REPLACE FUNCTION markov(nm text, ord int, len int) RETURNS text AS
$$
DECLARE
    sz integer;
    mtable text;
    ddl text;
    seed text;
    output text;
BEGIN
    mtable := 'markov_' || nm || '_' || format('%s', ord);
    ddl := 'CREATE TEMPORARY TABLE ' || mtable;
    ddl := ddl || '(key text PRIMARY KEY, val text)';
    EXECUTE ddl;
    
    RAISE NOTICE 'Starting...';

    SELECT length(data) INTO sz FROM markov_texts
    WHERE name = nm;

    /* generate Markov model map */
    FOR i IN 1..sz LOOP
        DECLARE
            t text;
            k text;
            v text;
            c int;
            q text;
        BEGIN
            IF i % 1000 = 0 THEN
                RAISE NOTICE 'Processed % n-grams', i;
            END IF;

            SELECT substring(data from i for ord + 1)
            INTO t FROM markov_texts WHERE name = nm;

            k := substring(t from 1 for ord);
            v := substring(t from 1 + ord for 1);

            q := 'SELECT count(*) FROM ' || mtable;
            q := q || ' WHERE key = $1';
            EXECUTE q INTO c USING k;

            IF c = 0 THEN
                q := 'INSERT INTO ' || mtable;
                q := q || ' VALUES($1, $2)';
                EXECUTE q USING k, v;
            ELSE
                q := 'UPDATE ' || mtable;
                q := q || ' SET val = val || $2';
                q := q || ' WHERE key = $1';
                EXECUTE q USING k, v;
            END IF;
        END;
    END LOOP;

    RAISE NOTICE 'Generating text...';

    /* generate text */
    /* get random seed */
    SELECT substring(data from (trunc(random()*(length(data)-ord))::int)+1 for ord)
    INTO seed
    FROM markov_texts
    WHERE name = nm;

    /* create output */
    output := seed;
    FOR i IN 1..len LOOP
        DECLARE
            q text;
            v text;
            l text;
        BEGIN
            q := 'SELECT val FROM ' || mtable;
            q := q || ' WHERE key = $1';
            EXECUTE q INTO v USING seed;
            
            l := substring(v from (trunc(random()*length(v)))::int+1 for 1);
            output := output || l;
            seed := substring(seed from 2) || l;
        END;
    END LOOP;

    ddl := 'DROP TABLE ' || mtable;
    EXECUTE ddl;

    RETURN output;
END;
$$ LANGUAGE plpgsql;
