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

Я пытаюсь сделать несколько сложное преобразование строк в PostgreSQL 9.0.1. Значения в my_col представляют собой длинные строки в формате:

'12345_sometext_X12B_1'
'12345_sometext_optionaltext_Y09B_1'
'12345_sometext_optionaltext_X12A_1'

Мне нужно перевести часть «X12» в известное числовое значение, есть несколько разных известных значений (до 5).

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

select
          regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\\d*$'), -- returns {'X12'}
         (regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\\d*$'))[1], -- returns 'X12'
    case (regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\\d*$'))[1]
        when 'X12' then '1200'
        when 'Y09' then '950'
        else '?' end -- should return '1200' but throws error
from my_table;

Вместо этого я получаю сообщение об ошибке:

ERROR: set-valued function called in context that cannot accept a set
SQL state: 0A000

Может ли кто-нибудь посоветовать мне?


postgresql regex postgresql-9.0 set-returning-functions
person pstanton    schedule 09.10.2012    source источник
comment
Во-первых: Обновите, у вас устаревшая версия. Обновите до 9.0.10 для исправления ошибок, улучшения безопасности и стабильности. Дамп и перезагрузка не требуются, просто установите новые бинарники.   -  person Craig Ringer    schedule 09.10.2012

Ответы (2)


arrow_upward
8
arrow_downward

Даны данные:

create table my_table(my_col text);
insert into my_table(my_col) values
('12345_sometext_X12B_1'),
('12345_sometext_optionaltext_Y09B_1'),
('12345_sometext_optionaltext_X12A_1'),
('nomatch');

приведенный выше запрос действительно вызывает ошибку, о которой вы сообщаете. Очень странно, потому что:

SELECT pg_typeof((regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\\d*$'))[1]);

возвращает «текст». Однако на самом деле должно быть указано setof text, и это ловушка: regex_matches — это функция, возвращающая множество. У них есть ... интересное ... поведение при вызове вне предложения FROM в PostgreSQL.

Из сопоставления шаблонов:

Функция regexp_matches возвращает текстовый массив всех захваченных подстрок, полученных в результате сопоставления с шаблоном регулярного выражения POSIX. Он имеет синтаксис regexp_matches(строка, шаблон [, флаги]). Функция может не возвращать ни одной строки, одну строку или несколько строк.

Попробуйте переформулировать свой запрос, чтобы использовать подзапрос для вызова SRF. Однако это не удастся, если сопоставитель вернет более одной строки:

SELECT 
  CASE (SELECT x[1] FROM regexp_matches(my_col, E'^.*_([^_]*)[A-Z]{1}_\\d*$') x)
    WHEN 'X12' THEN '1200'
    WHEN 'Y09' THEN '950'
    ELSE '?'
  END
FROM my_table;

Хотите увидеть, насколько странными являются SRF в SELECT в Pg? Сравните результаты этих запросов:

SELECT generate_series(1,10), generate_series(1,15);

а также:

SELECT generate_series(1,10), generate_series(1,20);

1-й производит 30 строк. 2-й дает 20. Повеселитесь, объясняя, почему. Более чем одна SRF в списке SELECT в Pg дает сумасшедшие, хотя иногда и полезные результаты.

PostgreSQL 9.3 поддерживает стандартное для SQL предложение LATERAL благодаря Тому Лейну, которое обеспечивает разумную и четко определенную альтернативу текущему поведению.

person Craig Ringer    schedule 09.10.2012
comment
Постепенное изменение аргументов на generate_series() ужасает, видя изменение количества строк. - person All Workers Are Essential; 26.09.2014
comment
@cpburnz ... и совершенно бессмысленный способ его работы? Ага. Вот почему я использую LATERAL, никогда не устанавливая возвращающие функции в списке SELECT. - person Craig Ringer; 26.09.2014

arrow_upward
2
arrow_downward

regexp_matches() возвращает SETOF text[] (набор текстовых массивов), который полезен при множественных совпадениях одного шаблона в одной строке. Но это просто неправильный инструмент для этой задачи.

Используйте substring()< /a> с регулярным выражением. Он возвращает text. Повторное использование демо-таблицы в ответе @Craig:

SELECT CASE substring(my_col, '^.*_([^_]*)[A-Z]_\d*$')
         WHEN 'X12' THEN '1200'
         WHEN 'Y09' THEN '950'
         ELSE            '?'
       END As result
FROM   my_table;

Возвращает:

 result
--------
 1200
 950
 1200
 ?

Также немного упростил регулярное выражение. {1} был просто шумом.

Если вам нужно оптимизировать производительность, попробуйте обойтись без регулярных выражений, которые являются мощными, но сравнительно дорогими. Что-то типа:

reverse(right(split_part(reverse(my_col), '_', 2), -1))

Выглядит сложнее, но все же быстрее в моих тестах.

person Erwin Brandstetter    schedule 10.10.2012
comment
По какой-то причине substring имеет здесь значительно худшую производительность (хотя это решает мою конкретную проблему, аналогичную приведенной выше). В вашем ответе отсутствует вопрос о том, что вопрос включает индексатор массива: regexp_matches(src, regex)[1]. - person Rhys van der Waerden; 20.10.2017
comment
@RhysvanderWaerden: Эта причина была бы интересной, я не могу ее воспроизвести. substring() в моих тестах работает быстрее (как и ожидалось). И почему в ответе ничего не говорится об индексе массива? SRF недействителен там, где он используется, с индексом массива или без него. Я немного обновил и добавил еще более быструю альтернативу. - person Erwin Brandstetter; 21.10.2017