Блог

Мой клиент хочет иметь возможность сопоставлять набор данных с массивом регулярных выражений, что означает:

 table:
name   | officeId (foreignkey)
--------
bob    | 1
alice  | 1 
alicia | 2
walter | 2
  

и он хочет сделать что-то в этом роде:

получите мне все записи офисов (OfficeId), где есть член с

 ANY name ~ ANY[.*ob, ali.*]
meaning
ANY of[alicia, walter] ~ ANY of [.*ob, ali.*] results in true
  

К сожалению, я не мог понять это сам:/.

Редактировать

Реальная проблема отсутствовала в исходном описании:

Я не могу использовать select disctinct officeId .. where name ~ ANY[.*ob, ali.*] , потому что:

Это приложение хранит данные в столбцах postgres-xml, что означает, что я действительно имею (после оценки xpath('/data/clients/name/text()'))::text[] ):

 table:
name              | officeId (foreignkey)
-----------------------------------------
[bob, alice]      | 1
[anthony, walter] | 2
[alicia, walter]  | 3
  

Проблема в этом. И «вы этого не делаете, это ужасно, зачем вам делать это так, храните это так, как будто оно предназначено для хранения в базе данных отношений, используйте базу данных без sql для хранения на основе документов, используйте json». вариантов нет.

Я застрял с этой моделью данных.

Это выглядит довольно ужасно, но единственный способ, которым я могу придумать такую вещь, — это гибрид перекрестного соединения и полусоединения. На небольших наборах данных это, вероятно, будет работать довольно хорошо. Я полагаю, что на больших наборах данных компонент перекрестного соединения может сильно ударить по вам.

Проверьте это и дайте мне знать, работает ли это с вашими реальными данными:

 with patterns as (
  select unnest(array['.*ob', 'ali.*']) as pattern
)
select
  o.name, o.officeid
from
  office o
where exists (
  select null
  from patterns p
  where o.name ~ p.pattern
)
  

Полусоединение помогает защитить вас от случаев, когда у вас есть имя типа «alicia nob», которое соответствовало бы нескольким шаблонам поиска, иначе оно возвращалось бы при каждом совпадении.

Вы можете преобразовать массив в текст.

 SELECT * FROM workers WHERE (xpath('/data/clients/name/text()', xml_field))::text ~ ANY(ARRAY['wal','ant']);
  

При преобразовании массива строк в текст строки, содержащие специальные символы или состоящие из ключевых слов, заключаются в двойные кавычки, как будто {jimmy,"walter, james"} это две записи. Также при сопоставлении с ~ ним сопоставляется с любой частью строки, а не с тем, LIKE где она сопоставляется со всей строкой.

Вот что я сделал в своей тестовой базе данных:

 test=# select id, (xpath('/data/clients/name/text()', name))::text[] as xss, officeid from workers WHERE (xpath('/data/clients/name/text()', name))::text ~ ANY(ARRAY['wal','ant']);
 id |           xss           | officeid 
---- ------------------------- ----------
  2 | {anthony,walter}        |        2
  3 | {alicia,walter}         |        3
  4 | {"walter, james"}       |        5
  5 | {jimmy,"walter, james"} |        4
(4 rows)