liveBook · Manning

Result highlighter

As explained in Just Use Postgres!

A result highlighter generates text fragments in which terms matching a search query are visibly marked.

A result highlighter generates text fragments from matching documents and visibly marks terms that satisfy a search query. PostgreSQL provides this capability through ts_headline, which can also accept a full-text configuration and output options. section 6.5

Highlight the highest-ranked movie

SELECT id, name, description, ts_headline(description, to_tsquery('pirates')) AS fragments, ts_rank(lexemes, to_tsquery('pirates')) AS rank FROM omdb.movies WHERE lexemes @@ to_tsquery('pirates:B') ORDER BY rank DESC LIMIT 1; The result can return a fragment such as "<b>pirate</b> Captain Jack is in a battle with the ocean itself." The filter searches weighted lexemes, while ts_headline highlights matches in the original description. section 6.5

Listing 6.15 Customizing ts_headline to show additional fragments
SELECT id, name, description,
  ts_headline(description, to_tsquery('pirates'),
    'MaxFragments=3, MinWords=5, MaxWords=10, 
     FragmentDelimiter=<ft_end>') AS fragments,
  ts_rank(lexemes, to_tsquery('pirates')) AS rank
FROM omdb.movies
WHERE lexemes @@ to_tsquery('pirates:B')
ORDER BY rank DESC LIMIT 1;

Customize headline fragments with MaxFragments, MinWords, MaxWords, and FragmentDelimiter options. section 6.5

How Just Use Postgres! covers it

How deep it goes
Explains ts_headline, weighted filtering, ranking, highlighting, and fragment options through executable SQL, without deeper implementation details.
Examples
Uses movie descriptions, the term pirates, weighted lexemes, ranked results, and customized multi-fragment output.
What it assumes
Assumes full-text search documents, tsquery expressions, lexeme matching, and ranking; does not establish those concepts from zero.
What you can do after
Apply it via PostgreSQL's ts_headline function to produce highlighted search fragments and customize their presentation.
How it connects
Connects full-text search, search queries, lexemes, ranking, weighted lexemes, and PostgreSQL configuration.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.