liveBook · Manning

Ts_headline

As explained in Just Use Postgres!

ts_headline is a PostgreSQL function that returns text fragments with terms matching a full-text query highlighted.

ts_headline returns fragments of a text document with terms matching a full-text query highlighted. Use it when a search result needs readable excerpts for display or further processing. section 6.5

Highlight a movie description

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 highest-ranked result highlights a matching term in a description fragment, such as <b>pirate</b> Captain Jack is in a battle with the ocean itself.

Customize fragments

Set MaxFragments=3, MinWords=5, MaxWords=10, and FragmentDelimiter=<ft_end> to return more controlled excerpts separated by a custom delimiter.

How Just Use Postgres! covers it

How deep it goes
Explains the function, execution flow, and fragment options through SQL examples, without covering implementation internals.
Examples
Uses ranked movie descriptions, pirate queries, highlighted excerpts, and customized fragment delimiters.
What it assumes
Assumes familiarity with PostgreSQL full-text queries, lexemes, and ranking while demonstrating highlighting through movie-search SQL.
What you can do after
Apply it via PostgreSQL to produce highlighted excerpts with configurable fragment counts, lengths, and delimiters.
How it connects
Connects full-text search with tsquery, lexemes, ts_rank, ranking, and full-text configuration.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

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.