PostgreSQL procedure that concatenates all prefix substrings for a given string. Helpful for generating full-text search indices.

prefixes.sql

CREATE FUNCTION prefixes(varchar, integer) RETURNS varchar AS $$
DECLARE
    result varchar;
    minlength integer;
BEGIN
    SELECT GREATEST($2, 0) INTO length;
    WITH RECURSIVE t(value, n) AS (
        SELECT $1, length($1)
    UNION ALL
        SELECT substring(value for n-1), n-1 FROM t WHERE n>minlength
    )
    SELECT string_agg(value, ' ') INTO result FROM t;
    RETURN result;
END
$$ LANGUAGE plpgsql;


-- Usage
--
-- postgres=# SELECT prefixes('github', 3);
--        prefixes
-- -----------------------
--  github githu gith git
-- (1 row)