sql - 与ANY()模式匹配

我有一个由3列组成的表，其中一列是非均匀长度（模块）的文本数组。我想创建一个派生表，这取决于modules数组中是否存在与特定文本模式/短语匹配的元素。元素名也可以改变，尽管它有一个公共短语。
下面给出了一个非工作示例：

    select machine_id, jobid,
    case
    when '%charmm%' LIKE ANY(modules) then 'CHARMM'
    when '%gaussian%' LIKE ANY(modules) then 'GAUSSIAN'
    else 'OTHERS'
    end as package, modules
    from jobapps limit 50;

在这种情况下，gaussiian可以有几个模块名：gaussian/16b01，gaussian/09e01。但我想把重点放在短语“gaussian”上，并创建一个新列，其中与文本模式“gaussian”匹配的所有条目都将被标记为“gaussian”。
这就是为什么我想像使用任何模块一样使用'%gaussian%'，但这不起作用。
我可以像任何（模块）一样使用：“gaussian/09e01”，但在这种情况下，我必须写入所有可能的值，如果用户创建一个新的gaussian模块，那么将丢失该数据。
有没有办法解决上述问题？或者有更好的方法来实现这个目标吗？

    select machine_id, jobid,
    case
    when '%charmm%' LIKE ANY(modules) then 'CHARMM'
    when '%gaussian%' LIKE ANY(modules) then 'GAUSSIAN'
    else 'OTHERS'
    end as package, modules
    from jobapps limit 50;

我想弄点像：

    machine_id | jobid  | package  |         modules
    ------------+--------+----------+--------------------------
    6 | 1884 | CHARMM   | {charmm}
    2 | 2305 | CHARMM   | {charmm}
    6 | 786  | GAUSSIAN | {gaussian/09e1}
    7 | 1956 | CHARMM   | {charmm}
    3 | 72037| NAMD     | {namd,intel/2018}

最佳答案

LIKE运算符要求模式位于右侧，而ANY()表达式将始终使用数组成员作为右侧操作数，因此我认为没有一个简单的解决方法。
您可以创建自己的“类反转”运算符，使操作数反转：

create function reverse_like(text,text) returns boolean as
'select $2 like $1'
language sql immutable;

create operator <~~ (
  function = reverse_like,
  leftarg = text,
  rightarg = text
);

…然后使用以下命令查询数组：

case
when '%charmm%' <~~ ANY(modules) then 'CHARMM'
when '%gaussian%' <~~ ANY(modules) then 'GAUSSIAN'

如果您不想为这个查询创建一个新的运算符，我想您需要做很长的一段时间，取消对数组的测试并检查子查询中的内容：

case
when exists (select 1 from unnest(modules) u(v) where v like '%charmm%') then 'CHARMM'
when exists (select 1 from unnest(modules) u(v) where v like '%gaussian%') then 'GAUSSIAN'

... 或者：

case
when (select bool_or(v like '%charmm%') from unnest(modules) u(v)) then 'CHARMM'
when (select bool_or(v like '%gaussian%') from unnest(modules) u(v)) then 'GAUSSIAN'