Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phnRb-0004wO-LQ for pgsql-general@arkaria.postgresql.org; Thu, 30 Mar 2023 08:15:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phnRa-0003gP-J9 for pgsql-general@arkaria.postgresql.org; Thu, 30 Mar 2023 08:15:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phnRa-0003fn-2K for pgsql-general@lists.postgresql.org; Thu, 30 Mar 2023 08:15:38 +0000 Received: from mail-wm1-x335.google.com ([2a00:1450:4864:20::335]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1phnRX-0000BP-3f for pgsql-general@lists.postgresql.org; Thu, 30 Mar 2023 08:15:37 +0000 Received: by mail-wm1-x335.google.com with SMTP id u11-20020a05600c19cb00b003edcc414997so11187475wmq.3 for ; Thu, 30 Mar 2023 01:15:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680164133; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=W6QR0dwjgcu6Jr37aRc3PD/zxbcc9WWggGHiKDdWqcQ=; b=ByxR6De3HPnwdYH7M0KzLBsndU8nXi/z71EARprgHeIny+va1sOvWafSiP5LdosMr3 VVJQuVnTIGYz7xXJAWlsmto89F5guHlR6dqo43YAjTsjZ6gSlOQJumTYFNdnASoD1lTw F46hfoWatrdCayApqbeIfpG7gfvvIWkR7m+Siu0wdWqsEXf2hx1U+YKVkOQc7JF1neez kNG245z5uxGKRItXhR4d2bhtLfssKHSJcAcLbHuh7KOj1g7G/9SNz6rwGPHlsKRO7QfL 0uEujBWYXMmPSOyR3CDEdbXpYV75tfvQia03loNhGdOVE9a53ErzjWGJ+yJWhwXHsl2a mbgQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680164133; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=W6QR0dwjgcu6Jr37aRc3PD/zxbcc9WWggGHiKDdWqcQ=; b=cxIQ18ddoR4N5eat3N4Qpd+QSl0du61bvh88RE7GMpcttysZloLbDwSluUlP5cqQ2n FLHAOjVjPIzQQ4l8Rfw+9BkJMCI6lVlKCiaiz3DzyIiM00srbJPgM6wYJUJUPXtziPNa 46eH7aXc1fPUwNu8jQ+j7aalZzETnFbsrJ/Kkle+vC1WIN+/8sGjfoaFJtrn5yCh9FD8 /G+f2wC1FF14LxsrZJVl7koRBrfGz+0ANrdtxlD4qBJ/zZItJn/zzXbWSFllGS3M1loZ h92Bg1givCrFVxWAgaHoFr7Ds+K6YyRLdjUa6VvbmtCjD40OCIBpP/1b3pvXPJR+BtDs KxRQ== X-Gm-Message-State: AAQBX9f+ysH4NCCLhM3zsyVcGzLK/846zQGlC0O17NP9DrqhsPeUc/84 QtSuqXxR6gSoi2L6W3iHKEmAZLclbL8M0w== X-Google-Smtp-Source: AKy350aNUTNxNrFIe3rrwOfBeChhdsQhbWOO+/VzhN/wQ3ovre1w9uZOdwDfZch8bCYwE9evfIgvyw== X-Received: by 2002:a1c:7405:0:b0:3ef:62cd:1eb with SMTP id p5-20020a1c7405000000b003ef62cd01ebmr13850722wmc.7.1680164133378; Thu, 30 Mar 2023 01:15:33 -0700 (PDT) Received: from smtpclient.apple (adsl-70.176.58.226.tellas.gr. [176.58.226.70]) by smtp.gmail.com with ESMTPSA id bg2-20020a05600c3c8200b003eddf20ed5bsm5292658wmb.18.2023.03.30.01.15.32 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 30 Mar 2023 01:15:33 -0700 (PDT) From: Florents Tselai Content-Type: multipart/alternative; boundary="Apple-Mail=_D2BF46CC-E391-454D-90B3-33EAAC69DF1B" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.500.231\)) Subject: Multilang text search. Is this correct? Message-Id: <92C5D7E0-A9C6-441B-80D8-C86F80AB7F30@gmail.com> Date: Thu, 30 Mar 2023 11:15:20 +0300 To: pgsql-general X-Mailer: Apple Mail (2.3731.500.231) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_D2BF46CC-E391-454D-90B3-33EAAC69DF1B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 In my table (mix of text and jsonb columns ) I have text in multiple = languages.=20 I=E2=80=99d like search in all the possible regconfigs, so I=E2=80=99ve = 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(=E2=80=99TEXT') @@ = to_tsquery_multilang(=E2=80=98QUERY=E2=80=99); Looks like it=E2=80=99s working as expected from my prelim tests, but = anyone seeing any potential pitfalls?=20 Note: I=E2=80=99m using GIN & RUM indices as well. --Apple-Mail=_D2BF46CC-E391-454D-90B3-33EAAC69DF1B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 In my table = (mix of text and jsonb columns ) I have text in multiple = languages. 
I=E2=80=99d like search in all the possible = regconfigs, so I=E2=80=99ve 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(=E2=80=99TEXT') @@ =
to_tsquery_multilang(=E2=80=98QUERY=E2=80=99);
Looks like it=E2=80=99s working as =
expected from my prelim tests, but anyone seeing any potential =
pitfalls? 

Note: I=E2=80=99m using GIN =
& RUM indices as well.

= --Apple-Mail=_D2BF46CC-E391-454D-90B3-33EAAC69DF1B--