Tokenizer Functions

Every template of CREATE TEXT SEARCH DICTIONARY is also a scalar function of the same name: split_text, stem_words, generate_ngrams. The function takes the value to analyze first and then the template's own arguments — the same names, the same positional order, the same defaults as on the template's page — and returns the tokens as a list. It is an implicit instantiation of the tokenizer: split_text(body, case := 'lower') in a query produces exactly what a dictionary created AS split_text(case := 'lower') produces for body, so the functions let you analyze text ad hoc, preview a dictionary stage by stage, or tokenize a column without creating anything in the catalog. WITH (help) on CREATE TEXT SEARCH DICTIONARY prints the function form under every template.

SQL

SELECT split_text('The Quick fox-trot.', case := 'lower') AS tokens;

 tokens
----------------------
 {the,quick,fox,trot}

Values, lists and chains

Input	Behaviour
`VARCHAR`	The value is analyzed as one value, the way a dictionary analyzes one cell.
`VARCHAR[]`	Each element is analyzed on its own and the results are concatenated into one list, so a function applied to another function's result acts as the next stage of a pipeline.
`VARCHAR[]` for `generate_shingles` and `generate_wildcard_ngrams`	The list is one token stream: the elements are the base tokens the shingles or wildcard grams are built from.
`NULL`	A `NULL` value yields `NULL`; `NULL` elements of a list are skipped.
`VARCHAR[N]`	A fixed-size array is read as the list it is, in the value position and in a list-valued option.

Nesting the functions is the pipeline: the inner call produces the list the outer call re-analyzes element by element, so stem_words(normalize_tokens(split_text(v, case := 'lower'), 'en_US.UTF-8', accent := false), 'en_US.UTF-8') is the dictionary split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false) | stem_words('en_US.UTF-8'):

SQL

SELECT stem_words(normalize_tokens(split_text('The Runners Café', case := 'lower'), 'en_US.UTF-8', accent := false), 'en_US.UTF-8') AS tokens;

 tokens
-------------------
 {the,runner,cafe}

SQL

CREATE TEXT SEARCH DICTIONARY english AS    split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false) | stem_words('en_US.UTF-8');
SELECT stem_words(normalize_tokens(split_text('The Runners Café', case := 'lower'), 'en_US.UTF-8', accent := false), 'en_US.UTF-8')       = ts_lexize('english', 'The Runners Café') AS same;

 same
------
 t

A list literal is analyzed element by element:

SQL

SELECT normalize_tokens(['Café', 'ÉCOLE'], 'en_US.UTF-8', case := 'lower', accent := false) AS tokens;

 tokens
--------------
 {cafe,ecole}

generate_shingles and generate_wildcard_ngrams need a token stream, so they take the output of another function; a bare VARCHAR counts as a single token:

SQL

SELECT array_length(generate_shingles(split_text_csv('quick brown fox', ' '), 2, 2), 1) AS terms;

 terms
-------
     5

A list carries no token positions, only order, so the two wrappers read every element as one position and treat neighbours as adjacent. A dictionary sees the real positions instead, which differ wherever a stage emits several tokens at one position: the grams of generate_ngrams are alternatives for the same word, and the expansions of expand_solr_synonyms are alternatives for the same token. Shingling such a stage therefore belongs in a dictionary: the function generate_shingles(generate_ngrams(v, 2, 3), 2, 2) pairs each gram with the next one in the list, where the dictionary generate_shingles(generate_ngrams(2, 3), 2, 2) pairs across positions and leaves same-position grams unpaired.

The value may come from a column; the options may not. Every option is folded to a constant when the query is bound, because the tokenizer is built once per query, so an option that varies per row fails with <name>(): option "<option>" must be a constant. A NULL option leaves it at its default, and a misspelled or missing option fails to bind with No function matches the given name and argument types, followed by the candidate signatures.

An option whose value is a list of strings takes either spelling of the dictionary form: the list, remove_stopwords(v, stopwords := ['the']), or the string its template page documents, remove_stopwords(v, stopwords := '"the"') and encode_geopoint(v, latitude := 'loc/lat'). The candidate signature spells that out as UNION(str VARCHAR, list VARCHAR[]), which a fixed-size VARCHAR[N] also satisfies; any other type fails to bind.

SQL

CREATE TABLE articles (id INTEGER, body VARCHAR);
INSERT INTO articles VALUES (1, 'The Runners'), (2, 'were Running');
SELECT id, stem_words(split_text(body, case := 'lower'), 'en_US.UTF-8') AS tokens FROM articles ORDER BY id;

 id | tokens
----+--------------
  1 | {the,runner}
  2 | {were,run}

SQL

SELECT split_text(body, case := body) FROM articles;

error must be a constant

The result type follows the tokenizer: VARCHAR[] for every text template, BLOB[] for collate_tokens, encode_geopoint and encode_geojson, whose terms are binary. A template that stores a per-document payload alongside its terms, such as encode_geojson with a non-source coding, returns only the terms.

Four templates have no function form, because SQL already expresses them: keyword is the list literal [value], pipeline is nesting, union is list_concat of two calls, and a sql stage is the expression itself.

A stored dictionary has no function form: it is an analyzer instance rather than a template, so a query runs it through ts_lexize and an analyzer expression names it as a stage.

Functions

Each signature lists the value first and then the template's options with their defaults; a required option has none. Option semantics, constraints and error messages are on the page each row links to.

Function	What it does
`split_text(value, case := 'none', break := 'alpha')`	Split text into words, sentences, lines, paragraphs or grapheme clusters on Unicode boundaries, optionally case-folded
`split_text_icu(value, locale, break := 'alpha')`	Segment text into words or sentences with the ICU break iterator for `locale`
`split_text_csv(value, delimiter)`	Cut the value at every occurrence of `delimiter`, honouring `"` quoting
`split_by_delimiters(value, delimiters)`	Cut the value at every occurrence of any of the `delimiters`
`split_by_non_alpha(value, case := 'none', break := 'ascii')`	Runs of word characters, everything else a separator; `break` picks them: ASCII letters and digits, those plus every byte from `0x80` up, letters and digits or letters alone in any script, or everything but whitespace
`split_by_pattern(value, pattern, group := -1)`	Match or split with an RE2 regular expression
`expand_path(value, delimiter := '/', replacement := '', reverse := false, skip := 0)`	The cumulative prefixes of a delimited path
`normalize_tokens(value, locale := '', case := 'none', accent := true, form := 'nfc')`	Unicode normalization, locale-aware case conversion or full case folding, and accent stripping of each token
`stem_words(value, locale := '')`	The Snowball stem of each token for the language of `locale`, which has no usable default
`remove_stopwords(value, stopwords := [], stopwords_path := '', hex := false)`	Drop the listed tokens
`filter_tokens(value, min_length := 0, max_length := 0)`, `filter_tokens(tokens, lambda x: <predicate>)`	Drop the tokens outside a range of character lengths, or those a lambda rejects
`strip_html(value, join_inline_tags := false)`	The text runs between HTML markup with character references decoded and `script` and `style` content dropped; `join_inline_tags` keeps words whole across inline tags
`generate_ngrams(value, min_gram := 2, max_gram := 3, preserve_original := false, input_type := 'utf8', start_marker := '', end_marker := '', mode := 'all')`	Character n-grams of each token, including the prefix-anchored edge n-grams of `mode := 'only_prefix'`
`generate_sparse_ngrams(value, max_ngram_length := 16, covering := false, min_ngram_length := 3, min_cutoff_length := 0)`	Sparse variable-length n-grams for substring search
`generate_wildcard_ngrams(value, ngram_size := 3)`	Boundary-marked n-grams for wildcard and prefix matching; a list is the token stream, a `VARCHAR` one token
`generate_shingles(value, min_gram := 2, max_gram := 2, output_unigrams := true, fallback_unigrams := false, frequent_words := [], token_separator := ' ')`	Word n-grams over the token stream; a list is the token stream, a `VARCHAR` one token
`collate_tokens(value, locale := '')`	The collation sort key of each token for `locale`, as `BLOB[]`
`expand_solr_synonyms(value, synonyms)`	Synonym expansion from a Solr synonyms text
`expand_wordnet_synonyms(value, synonyms)`	Synonym expansion from WordNet prolog text
`classify_text(value, model_location := '', top_k := 1, threshold := 0)`	The labels a fastText model assigns to the value
`find_nearest_words(value, model_location := '', top_k := 1)`	The nearest word vectors of a fastText model
`encode_geopoint(value, latitude := [], longitude := [], max_cells := 20, min_level := 4, max_level := 23, level_mod := 1, optimize_for_space := false)`	The S2 cell terms of a `JSON` point, as `BLOB[]`; there is no list form
`encode_geojson(value, type := 'shape', coding := 'source', max_cells := 20, min_level := 4, max_level := 23, level_mod := 1, optimize_for_space := false)`	The S2 cell terms of a `JSON` geometry, as `BLOB[]`; there is no list form
`minhash(tokens, num_hashes)`	Reduce a token list to a MinHash signature; it has no template

Values, lists and chains​

Functions​

Values, lists and chains

Functions