Skip to content

Instantly share code, notes, and snippets.

@boserup
Created December 4, 2017 09:41
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save boserup/824e5c7c85c4a71c2522608946bc3ab9 to your computer and use it in GitHub Desktop.
SELECT *,
ts_rank(
SETWEIGHT(
TO_TSVECTOR(
'english',
COALESCE(search_view.name, '')
),
'A'
) || SETWEIGHT(
TO_TSVECTOR(
'english',
COALESCE(search_view.creator, '')
),
'B'
) || SETWEIGHT(
TO_TSVECTOR(
'english',
COALESCE(search_view.fulldesc, '')
),
'C'
),
to_tsquery('english', lower('test|kids|jonas'))
) AS rank
FROM search_view
WHERE
(
to_tsvector('english', COALESCE(search_view.name, ''))
|| to_tsvector('english', COALESCE(search_view.creator, ''))
|| to_tsvector('english', COALESCE(search_view.fulldesc, ''))
)
@@ to_tsquery('english', lower('test|kids|jonas'))
ORDER BY rank DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment