sql - 有条件地删除Array字段PostgreSQL中的项目

我正在构建一种字典应用程序，并且有一个用于存储单词的表格，如下所示:

id | surface_form | examples
-----------------------------------------------------------------------
 1 | sounds       | {"It sounds as though you really do believe that",
   |              |  "A different bell begins to sound midnight"}

其中surface_form是CHARACTER VARYING类型，而examples是CHARACTER VARYING的数组字段

由于示例是从另一个API自动生成的，因此可能不包含确切的“surface_form”。现在，我只想在示例中保留包含精确的surface_form的句子。例如，在给定的示例中，只有第一个句子包含sounds，因此保留第二个句子，因为它仅包含sound。

问题是我陷入了如何编写查询和/或plSQL存储过程以更新examples列，使其仅包含所需语句的问题。

最佳答案

最紧凑的解决方案(但不一定是最快的解决方案)是编写一个函数，该函数传递一个正则表达式和一个数组，然后返回一个仅包含与正则表达式匹配的项的新数组。

create function get_matching(p_values text[], p_pattern text)
  returns text[]
as
$$
declare
  l_result text[] := '{}'; -- make sure it's not null
  l_element text;
begin
  foreach l_element in array p_values loop

    -- adjust this condition to whatever you want
    if l_element ~ p_pattern then
      l_result := l_result || l_element;
    end if;

  end loop;
  return l_result;
end;
$$
language plpgsql;

if条件仅是示例。您需要将其调整为确切存储在surface_form列中的内容。也许您需要测试正则表达式的单词边界，或者使用简单的instr()进行测试-您的问题尚不清楚。

清理表变得如此简单:

update the_table
   set examples = get_matching(examples, surface_form);

但是整个方法对我来说似乎是有缺陷的。如果将示例存储在适当规范化的数据模型中，效率将会大大提高。