SELECT
  o.*,
  ts_rank_cd(o.search_vector, query, 32) AS rank
FROM offres o,
  websearch_to_tsquery('french', :q) AS query
WHERE
  o.search_vector @@ query
  AND o.status = 'PUBLISHED'
ORDER BY rank DESC, o.published_at DESC
LIMIT :size OFFSET :page * :size;

SELECT DISTINCT titre
FROM offres
WHERE status = 'PUBLISHED'
  AND titre % :q              -- similarity > threshold
ORDER BY similarity(titre, :q) DESC, titre
LIMIT 10;

CREATE TRIGGER offres_search_vector_update
BEFORE INSERT OR UPDATE ON offres
FOR EACH ROW EXECUTE FUNCTION update_offres_search_vector();