postgresql - 如何在 PostgreSQL 中执行数据库范围的全文搜索？

Question

我有一个包含大约 500 个表的 PostgreSQL 数据库。每个表都有一个唯一的 ID 列，名为 id和一个名为 user_id 的用户 ID 列.我想对特定用户的所有这些表中的所有 varchar 列执行全文搜索。我今天用 ElasticSearch 做这件事，但我想简化我的架构。我知道我可以将全文搜索列添加到所有表中，例如 stored generated columns然后添加索引以进行快速全文搜索:

ALTER TABLE pgweb
    ADD COLUMN textsearchable_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);

但是，我不熟悉如何有效地进行跨表搜索。也许是所有 textsearchable_index_col 列的 View ？我希望结果类似于表名和 id匹配行的。例如:

 table_name  |  id   
-------------+-------
 table1      |  492
 table42     |  20

如果重要的话，我使用 Ruby on Rails 作为 ActiveRecord 的客户端。我在 Digital Ocean 使用托管的 PostgreSQL 13 数据库，所以我将无法安装自定义 psql 插件。

Answer 1

也许这不是您要寻找的答案，因为我不确定是否有更好的方法，但首先我会尝试使该过程自动化。
我将进行两个动态查询，第一个创建列 textsearchable_index_col(在每个表中至少有一个 varchar 列)，另一个在该列上创建索引(每个表一个索引)。
您可以为每个“字符变化”列添加一个 textsearchable_index_col 列，而不是只连接所有“字符变化”列，但在这种情况下，我将按照您的建议为每个表创建一个 textsearchable_index_col 列。
我假设表模式为“public”，但您可以使用真实的。

-- Create columns textsearchable_index_col:

SELECT 'ALTER TABLE ' || table_schema || '.' || table_name || E' ADD COLUMN textsearchable_index_col tsvector GENERATED ALWAYS AS (to_tsvector(\'english\', coalesce(' ||
  string_agg(column_name, E', \'\') || \' \' || coalesce(') || E', \'\'))) STORED;'
FROM information_schema.columns
WHERE table_schema = 'public' AND data_type IN ('character varying')
GROUP BY table_schema, table_name;


-- Create indexes on textsearchable_index_col columns:

SELECT 'CREATE INDEX ' || table_name || '_textsearch_idx ON ' || table_schema || '.' || table_name || ' USING GIN (textsearchable_index_col);'
FROM information_schema.columns
WHERE table_schema = 'public' AND data_type IN ('character varying')
GROUP BY table_schema, table_name;

然后我将使用一个动态查询来创建一个查询(使用 UNION)来搜索所有 textsearchable_index_col 列:
您需要通过参数(user_id 和搜索的文本)替换问号，并取出最后一个“UNION ALL”

SELECT E'SELECT \'' || table_name || E'\' AS table_name, id FROM ' || table_schema || '.' || table_name || E' WHERE user_id = ? AND textsearchable_index_col' || ' @@ to_tsquery(?) UNION ALL'    
FROM information_schema.columns
WHERE table_schema = 'public' AND data_type IN ('character varying')
GROUP BY table_schema, table_name;