Как использовать regexp_matches() в операторе UPDATE?

Примечания

1.
Вам не нужно основывать коррелированный подзапрос на отдельном экземпляре базовой таблицы (как предполагают два ответа). Это будет делать больше работы ни за что.

2.
В простых случаях конструктор ARRAY дешевле, чем array_agg(). Видеть:

• Почему array_agg() медленнее чем неагрегатный конструктор ARRAY()?

3.
Я использую регулярное выражение без упреждающие и ретроспективные ограничения и круглые скобки вместо этого: href="([^"]+)

См. запрос 1.

Это работает, потому что подвыражения в скобках перехватываются regexp_matches() (и несколькими другими функциями регулярных выражений Postgres). Таким образом, мы можем заменить более сложные ограничения простыми скобками. Руководство по regexp_match():

Если совпадение найдено и pattern не содержит подвыражений в скобках, результатом является одноэлементный текстовый массив, содержащий подстроку, соответствующую всему шаблону. Если совпадение найдено и *pattern* содержит подвыражения в круглых скобках, то результатом является текстовый массив, чей n-й элемент является подстрокой, соответствующей n-му подвыражению в круглых скобках шаблон

И для regexp_matches():

Эта функция не возвращает ни одной строки, если совпадений нет, одну строку, если совпадение есть и флаг g не задан, или строк N, если совпадений N и задан флаг g. Каждая возвращаемая строка представляет собой текстовый массив, содержащий всю совпадающую подстроку или подстроки, соответствующие заключенным в скобки подвыражениям шаблона, как описано выше для regexp_match.

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

При передаче результирующих одномерных массивов в array_agg() (или конструктор ARRAY), который создает двумерный массив, что возможно только с тех пор, как Postgres 9.5 добавил вариант array_agg(), принимающий ввод массива. Видеть:

• Есть ли что-то вроде функции zip() в PostgreSQL, которая объединяет два массива?

Однако цитирует руководство:

все входы должны иметь одинаковую размерность и не могут быть пустыми или NULL

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

1. Возьмите только первый элемент с (regexp_matches(...))[1]. См. запрос 2.

2. Разложите массивы и используйте string_agg() для базовых элементов. См. запрос 3.

Каждый подход работает и здесь.

Запрос 1

UPDATE tbl t
SET    col = (
   SELECT array_to_string(ARRAY(SELECT regexp_matches(col, 'href="([^"]+)', 'g')), ',')
   );

Столбцам без совпадений присваивается значение '' (пустая строка).

Запрос 2

UPDATE tbl
SET    col = (
   SELECT string_agg(t.arr[1], ',')
   FROM   regexp_matches(col, 'href="([^"]+)', 'g') t(arr)
   );

Столбцам без совпадений присваивается значение NULL.

Запрос 3

UPDATE tbl
SET    col = (
   SELECT string_agg(elem, ',')
   FROM   regexp_matches(col, 'href="([^"]+)', 'g') t(arr)
        , unnest(t.arr) elem
   );

Столбцам без совпадений присваивается значение NULL.

db‹>fiddle здесь (с расширенным тестовым примером)

Вы можете использовать коррелированный подзапрос, чтобы иметь дело с оскорбительной функцией, возвращающей набор (это regexp_matches). Что-то вроде этого:

update mytable
set column = (
    select array_to_string(array_agg(x), ',')
    from (
        select regexp_matches(t2.c, '(?<=href=").+?(?=\")', 'g')
        from t t2
        where t2.id = t.id
    ) dt(x)
)

Вы все еще застряли с мерзостью «CSV в столбце», но это отдельная проблема и, по-видимому, не проблема для вас.

Построение на основе подхода mu слишком короткое с немного другим регулярным выражением и функцией COALESCE для сохранения значений, не содержащих ссылок href:

UPDATE a 
SET    bad_data = COALESCE(
  (SELECT Array_to_string(Array_agg(x), ',') 
   FROM   (SELECT Regexp_matches(a.bad_data, 
                                 '(?<=href=")[^"]+', 'g' 
                                ) AS x 
           FROM   a a2 
           WHERE  a2.id = a.id) AS sub), bad_data
);

скрипт SQL