Skip to content

Instantly share code, notes, and snippets.

@dlo
Last active November 25, 2021 03:26
Show Gist options
  • Save dlo/501ad13f04fcbbe520be to your computer and use it in GitHub Desktop.
Save dlo/501ad13f04fcbbe520be to your computer and use it in GitHub Desktop.
PostgreSQL procedure that concatenates all prefix substrings for a given string. Helpful for generating full-text search indices.
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment