英文:
postgresql function returning every row containing one of the word from a string
问题
我在 pgAdmin 4 中创建了以下函数，用于返回具有在其“synopsis”列中包含特定单词的电子书的 JSON 数据：
CREATE OR REPLACE FUNCTION searchA (searchEntry varchar(255)) RETURNS json LANGUAGE plpgsql
AS
$$
BEGIN
RETURN jsonb_agg(row_to_json(a)) FROM (SELECT * FROM ebook WHERE to_tsvector(synopsis) @@ to_tsquery(searchEntry)) a;
END;
$$
但我希望该函数能够返回包含字符串中至少一个单词的所有电子书。我该如何做到？
英文:
I have create the following function in pgAdmin 4 for returning as Json the ebooks having a word in their column 'synopsis':
CREATE OR REPLACE FUNCTION searchA (searchEntry varchar(255)) RETURNS json LANGUAGE plpgsql
AS
$$
BEGIN
RETURN jsonb_agg(row_to_json(a)) FROM (SELECT * FROM ebook WHERE to_tsvector(synopsis) @@ to_tsquery(searchEntry)) a;
END;
$$
but I would like for this function to be able return every ebook who contains at least one of the word from a string character.
How can I do that ?
答案1
得分: 1
如果我理解您正确，那么
如果searchEntry = 'so very best application'
您想查看所有包含这些单词的记录。
您可以使用以下语法来实现：
WHERE to_tsvector(synopsis) @@ to_tsquery('so | very | best | application'); -- 这是OR逻辑操作。
WHERE to_tsvector(synopsis) @@ to_tsquery('so & very & best & application'); -- 这是AND逻辑操作。
而且，您可以使用替换函数将您的searchEntry字符串转换为上述格式，例如
searchEntry = replace(searchEntry, ' ', ' | ');
英文:
If I understand you correctly, then
If searchEntry = 'so very best application'
You want to view all records which has any of these words.
You can do it using this syntaxis:
WHERE to_tsvector(synopsis) @@ to_tsquery('so | very | best | application'); -- this is OR logic operation.
WHERE to_tsvector(synopsis) @@ to_tsquery('so & very & best & application'); -- this is AND logic operation.
And you can converting your searchEntry string to above format using replace function, for example
searchEntry = replace(searchEntry, ' ', ' | ');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论