Обновите несколько столбцов, которые начинаются с определенной строки

Я пытаюсь обновить группу столбцов в БД для тестирования функции. У меня есть таблица, созданная с помощью спящего режима, поэтому все столбцы, созданные для встроенного объекта, начинаются с одного и того же имени. Т.е. contact_info_address_street1, contact_info_address_street2, так далее.

Я пытаюсь выяснить, есть ли способ сделать что-то для воздействия:

UPDATE table SET contact_info_address_* = null;

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

Источник

2 ответа

Решение

Там нет удобного ярлыка извините. Если вам приходится много заниматься такими вещами, вы можете создать функцию для динамического выполнения sql и достижения своей цели.

CREATE OR REPLACE FUNCTION reset_cols() RETURNS boolean AS $$ BEGIN 
    EXECUTE (select 'UPDATE table SET ' 
                  || array_to_string(array(
                              select column_name::text 
                              from information_schema.columns 
                              where table_name = 'table' 
                              and column_name::text like 'contact_info_address_%'
                     ),' = NULL,') 
                  || ' = NULL'); 
    RETURN true; 
 END; $$ LANGUAGE plpgsql;

-- run the function
SELECT reset_cols();

Это не очень хорошо, хотя. Лучшей функцией была бы та, которая принимает префикс имени таблицы и столбца как аргументы. Который я оставлю в качестве упражнения для читателей:)

Источник

Вам нужен динамический SQL для этого. Поэтому вы должны быть готовы справиться с возможной инъекцией SQL.

Основной запрос

Основной запрос для создания необходимой команды DML может выглядеть следующим образом:

SELECT format('UPDATE tbl SET (%s) = (%s)'
               ,string_agg (quote_ident(attname), ', ')
               ,string_agg ('NULL', ', ')
             )
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    NOT attisdropped 
AND    attnum > 0
AND    attname ~~ 'foo_%';

Возвращает:

UPDATE tbl SET (foo_a, foo_b, foo_c) = (NULL, NULL, NULL);

  • Я использую " синтаксис списка столбцов " UPDATE сократить код и упростить задачу.

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

  • quote_ident() для имен столбцов предотвращает SQL-внедрение, а также необходимо для любых нестандартных имен столбцов.

  • Вы не упомянули свою версию Postgres. Агрегатная функция string_agg() требуется 9.0+.

Полная автоматизация с функцией PL/pgSQL 

CREATE OR REPLACE FUNCTION f_update_cols(_tbl regclass, _col_pattern text
                                        , OUT row_ct int, OUT col_ct int)
  RETURNS record AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT format('UPDATE tbl SET (%s) = (%s)'
                 ,string_agg (quote_ident(attname), ', ')
                 ,string_agg ('NULL', ', ')
                )
         ,count(*)::int
   INTO   _sql, col_ct
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped         -- no dropped columns
   AND    attnum > 0               -- no system columns
   AND    attname ~~ _col_pattern; -- only columns matching pattern

   -- RAISE NOTICE '%', _sql;      -- output generated SQL for debugging
   EXECUTE _sql;

   GET DIAGNOSTICS row_ct = ROW_COUNT;
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_update_cols(regclass, text)
 IS 'Updates all columns of table _tbl ($1)
that match _col_pattern ($2) in a LIKE expression.
Returns the count of columns (col_ct) and rows (row_ct) affected.';

Вызов:

SELECT * FROM f_update_cols('myschema.tbl', 'foo%');

  • Чтобы сделать функцию более практичной, она возвращает информацию, как описано в комментарии. Подробнее о получении статуса результата в plpgsql в руководстве.

  • Я использую переменную _sql держать строку запроса, чтобы я мог собрать количество найденных столбцов (col_ct) в том же запросе.

  • Тип идентификатора объекта regclass это самый эффективный способ автоматически избежать внедрения SQL (и очистить нестандартные имена) для имени таблицы. Вы можете использовать имена таблиц, уточненные схемой, чтобы избежать двусмысленности. Я бы посоветовал сделать это, если у вас есть несколько схем в вашей базе данных! Больше деталей в этом связанном вопросе:
    Имя таблицы как параметр функции PostgreSQL

-> Демоверсия SQLfiddle.

Источник
Другие вопросы по тегам