Postgres查询以计算匹配的字符串 [英] Postgres query to calculate matching strings

问题描述

我有下表:

id   description additional_info
123    XYZ          XYD

还有一个数组为:

[{It is known to be XYZ},{It is know to be none},{It is know to be XYD}]

我需要以一种方式映射两个内容，即对于表的每个记录，我都可以定义成功匹配的次数. 上面示例的结果将是:

I need to map both the content in such a way that for every record of table I'm able to define the number of successful match. The result of the above example will be:

id    RID    Matches
1     123    2

仅位置0和2处的内容与记录的description/additional_info匹配，因此结果中的Matches为2.

Only the content at position 0 and 2 match the record's description/additional_info so Matches is 2 in the result.

我正在努力将其转换为Postgres中的查询-动态SQL以精确地在PL/pgSQL函数中创建VIEW.

I am struggling to transform this to a query in Postgres - dynamic SQL to create a VIEW in a PL/pgSQL function to be precise.

推荐答案

如何同时处理同时匹配 description和additional_info的数组元素尚未定义.我假设您要将其计为1场比赛.

It's undefined how to deal with array elements that match both description and additional_info at the same time. I'll assume you want to count that as 1 match.

在结果中id = 1的来源也是不确定的.

It's also undefined where id = 1 comes from in the result.

一种方法是 unnest() 数组和LEFT JOIN主表到两个列中任何一个匹配项上的每个元素:

One way is to unnest() the array and LEFT JOIN the main table to each element on a match on either of the two columns:

SELECT 1 AS id, t.id AS "RID", count(a.txt) AS "Matches"
FROM   tbl t
LEFT   JOIN unnest(my_arr) AS a(txt) ON a.txt ~ t.description
                                     OR a.txt ~ t.additional_info
GROUP  BY t.id;

我使用正则表达式进行匹配.右侧字符串中的特殊字符，例如(.\?)等具有特殊含义.如果可能，您可能必须逃避这些攻击.

I use a regular expression for the match. Special characters like (.\?) etc. in the strings to the right have special meaning. You might have to escape those if possible.

您应该已经提到您在EXECUTE中使用了plpgsql函数.可能有2个错误:

You should have mentioned that you are using a plpgsql function with EXECUTE. Probably 2 errors:

1. 变量array_content在EXECUTE内部不可见，您需要使用USING子句传递值-或在不允许参数的CREATE VIEW语句中将其连接为字符串文字.

1. The variable array_content is not visible inside EXECUTE, you need to pass the value with a USING clause - or concatenate it as string literal in a CREATE VIEW statement which does not allow parameters.

在字符串'brand_relevance_calculation_‌​view'周围缺少单引号.在将其串联为标识符之前，它仍然是字符串文字.在这里将format()与%I结合使用非常好.

Missing single quotes around the string 'brand_relevance_calculation_‌​view'. It's still a string literal before you concatenate it as identifier. You did good to use format() with %I there.

演示:

DO
$do$
DECLARE
   array_content varchar[]:= '{FREE,DAY}'; 
BEGIN

EXECUTE format('
   CREATE VIEW %I AS
   SELECT id, description, additional_info, name, count(a.text) AS business_objectives
        , multi_city, category IS NOT NULL AS category
   FROM initial_events i
   LEFT JOIN unnest(%L::varchar[]) AS a(text) ON a.text ~ i.description
                                              OR a.text ~ i.additional_info'
 , 'brand_relevance_calculation_‌​view', array_content);

END
$do$;

这篇关于Postgres查询以计算匹配的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！