Блог

Я программно пишу SQL-запросы из пользовательского ввода. Я хочу показать пользователю для каждой возвращаемой строки, какая часть предоставленной им информации о запросе соответствует этой строке.

Например, предоставленная пользователем информация выглядит следующим образом:

 red:[11,202]
blue:[36]
green:[202]
yellow:[11,36]
 

Эта информация позволит построить следующий запрос:

 SELECT name, list FROM test WHERE list SIMILAR TO '%(11|202|36)%'
 

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

Я не могу добавить примеры попыток, так как не знаю правильного языка, с помощью которого можно получить информацию. Мое текущее решение до сих пор заключается в обработке данных строка за строкой после того, как запрос вернет результат в мой скрипт для добавления нового столбца, но я хотел бы знать, возможно ли это сделать с помощью postgres.

Вот дб-скрипка: https://www.db-fiddle.com/f/nMMB3wVFRTGeZgobmA4F6k/1

Я использую postgresql 12.3, однако при необходимости могу изменить версию.

Я согласен с Хэмбоном в том, что вам следует воспользоваться преимуществами массивов. Если вы не хотите создавать дополнительную таблицу, вы также можете сделать все это в одном запросе:

 select name, list,
  array_to_string(ARRAY[
      CASE WHEN list LIKE ANY(ARRAY['%', ' 2%']) then 'red' else null end,
      CASE WHEN list LIKE ANY(ARRAY['6%']) then 'blue' else null end,
      CASE WHEN list LIKE ANY(ARRAY[' 2']) then 'green' else null end,
      CASE WHEN list LIKE ANY(ARRAY['%', '6%']) then 'yellow' else null end
    ], ', ')
from test 
where list LIKE ANY(ARRAY['%', ' 2%', '6%', ' 2%', '%', '6%']);
 

Обратите внимание, что я использую КАК ЛЮБОЙ, а не ПОХОЖИЙ НА. Вы могли бы продолжать использовать АНАЛОГИЧНО, но мне кажется, что использовать его проще, ЧЕМ ЛЮБОЙ ДРУГОЙ.

Вот скрипка.

Похоже, вам действительно нужно воспользоваться самой превосходной поддержкой массивов PostgreSQL. Мне кажется, что я часто это говорю.

Если вы измените свою структуру на массивы:

 create table test2 (
  name varchar(255),
  list integer[]
)
 

Затем, условно, вы даже можете поместить свои данные в другую таблицу:

 create table matches (
  color text,
  list integer[]
);

insert into matches values 
('red', '{11,202}'),
('blue', '{36}'), 
('green', '{202}'), 
('yellow', '{11,36}');
 

Следующий запрос должен дать результаты, которые вы ищете:

 select
  t.name, t.list, array_agg (m.color)
from
  test2 t
  join matches m on
    m.list amp;amp; t.list
group by
  t.name, t.list