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

Question

我有一个PostgreSQL数据库，大约有500个表。每个表都有一个名为id的唯一ID列和一个名称为user_id的用户ID列。我想对特定用户的所有这些表中的所有varchar列执行全文搜索。我今天用ElasticSearch做这件事，但我想简化我的体系结构。我知道我可以将全文搜索列添加到所有具有存储生成列之类内容的表中，然后添加索引以进行快速全文搜索：

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列的视图？我希望结果类似于匹配行的表名和id。例如：

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

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

Answer 1

也许这不是你想要的答案，因为我不确定是否有更好的方法，但首先我会尝试自动化这个过程。

我将进行两个动态查询，第一个是创建列textsearchable_index_col(在每个至少有一个varchar列的表中(，另一个是在这些列上创建索引(每个表一个索引(。

您可以为每个"添加textsearchable_index_col列；字符变化"；列，而仅一个串联所有"列"；字符变化"；列，但在本例中，我将按照您的建议为每个表创建一个textsearchable_index_col列。

我假设表模式"；"公共"；但你可以用真正的。

-- 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;