pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Florents Tselai <florents.tselai@gmail.com>
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Multilang text search. Is this correct?
Date: Thu, 30 Mar 2023 11:15:20 +0300
Message-ID: <92C5D7E0-A9C6-441B-80D8-C86F80AB7F30@gmail.com> (raw)

In my table (mix of text and jsonb columns ) I have text in multiple languages. 
I’d like search in all the possible regconfigs, so I’ve come up with the following recipe:

CREATE FUNCTION to_tsvector_multilang(text) RETURNS tsvector AS
$$
SELECT to_tsvector('english', $1) ||
       to_tsvector('greek', $1) ||
       to_tsvector('simple', $1)
$$ LANGUAGE sql IMMUTABLE;

CREATE FUNCTION jsonb_to_tsvector_multilang(jsonb, jsonb) RETURNS tsvector AS
$$
SELECT jsonb_to_tsvector('english', $1, $2) ||
       jsonb_to_tsvector('simple', $1, $2) ||
       jsonb_to_tsvector('greek', $1, $2)
$$ LANGUAGE sql IMMUTABLE;

CREATE FUNCTION to_tsquery_multilang(query text) RETURNS tsquery AS
$$
SELECT websearch_to_tsquery('english', query) ||
       websearch_to_tsquery('simple', query) ||
       websearch_to_tsquery('greek', query)
$$ LANGUAGE sql IMMUTABLE;
Thus, in searching I use  to_tsvector_multilang(’TEXT') @@ to_tsquery_multilang(‘QUERY’);
Looks like it’s working as expected from my prelim tests, but anyone seeing any potential pitfalls? 

Note: I’m using GIN & RUM indices as well.

view thread (2+ messages)  latest in thread

Message-ID: <92C5D7E0-A9C6-441B-80D8-C86F80AB7F30@gmail.com>
Permalink:  ../92C5D7E0-A9C6-441B-80D8-C86F80AB7F30@gmail.com/
Also on:    postgresql.org/message-id/92C5D7E0-A9C6-441B-80D8-C86F80AB7F30@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: florents.tselai@gmail.com
  Subject: Re: Multilang text search. Is this correct?
  In-Reply-To: <92C5D7E0-A9C6-441B-80D8-C86F80AB7F30@gmail.com>
  References: <92C5D7E0-A9C6-441B-80D8-C86F80AB7F30@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox