3

I'm testing text search on PostgreSQL and I would like to search for multiple terms based on a custom dictionnary

I have :

CREATE TABLE events (name text NOT NULL, description text NOT NULL);

INSERT INTO events VALUES
  ('Phaeleh Live','Phaeleh, one of the UK''s most vital production talents and a key innovator')
 ,('Radar 009','Radar Radio''s weekly parties re-launch for 2018 with some of their biggest line-ups yet.')
 ,('14anger','Collector of techno classics, admirer of futuristic sequencing, he weaves these together for an eternal timed prophecy rave');

CREATE MATERIALIZED VIEW unique_lexeme AS
SELECT word FROM ts_stat(
$TS_STAT$
SELECT to_tsvector('simple', events.name) || to_tsvector('english', events.description) as document
  FROM events
$TS_STAT$);

Then for the query :

WITH word_similarity AS (
  SELECT word
  FROM unique_lexeme
  WHERE similarity(word, 'par') > 0.1 
  ORDER BY word <-> 'par'
  LIMIT 1
)
SELECT events_name,
       events_description
       FROM (
       SELECT events.name AS events_name,
         events.description AS events_description,
         to_tsvector('simple', events.name) || to_tsvector('english', events.description) as document
  FROM events) events_search, word_similarity
WHERE events_search.document @@ to_tsquery('english', word_similarity.word || ':*')
ORDER BY ts_rank(events_search.document, to_tsquery('english', word_similarity.word || ':*')) DESC;

But how could I make word_similarity able to return a list of matching term I will search after? For example if I provide ['wee','vit'] I will get ['week','vital'].

Can I do it with a recursive CTE? or do I need to write a for each loop in PL/pgSQL?

sqlfiddle

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.