PL/pgSQL: общий способ обновления N столбцов в триггерах?

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

create or replace function uc_on_insert()
returns trigger as
$$
declare
p_tbl varchar = TG_TABLE_NAME;
p_sch varchar = TG_TABLE_SCHEMA;
i varchar;
begin
 for i in 
 (select column_name
 from INFORMATION_SCHEMA.COLUMNS
 where 1=1
 and table_name ilike p_tbl
 and table_schema ilike p_sch
 and data_type ='character varying')
 loop
 execute 'new.' || i || ' = upper(new.' || i || ');';
 return new;
 end loop;
end;
$$ language plpgsql;

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

ERROR: syntax error at or near "new"
LINE 1: new.c1 = upper(new.c1);
 ^
QUERY: new.c1 = upper(new.c1);

Ожидаемый ввод будет, на любой таблице у меня есть этот триггер:

insert into table_one('a', 'b');
>> A, B

и если я поместил этот триггер в другую таблицу:

insert into table_two ('a', 3);
>> A, 3

и т.д..

1 ответ

Это очень сложный вопрос.

Ваша попытка связана с ошибкой, потому что текущая переменная строки NEW не видна внутри EXECUTE. И даже если бы это было, NEW - это тип строки (запись), а не массив. В отличие от элементов массива, поля строки не могут ссылаться на числовой индекс. Это вызовет всевозможные проблемы в SQL, потому что (в отличие от массива) каждое поле может иметь другой тип данных, а SQL ожидает знать тип данных для обработки заранее. Очень сложно.

Общий метод, предназначенный только для выбранных типов

К счастью, раньше мы столкнулись с аналогичной проблемой:

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

Функция триггера

CREATE OR REPLACE FUNCTION trg_uc_on_insert()
 RETURNS trigger AS
$func$
BEGIN
EXECUTE 'SELECT ' || array_to_string(ARRAY(
 SELECT CASE WHEN atttypid = 'varchar'::regtype
 -- atttypid = ANY('{text, bpchar, varchar}'::regtype[])
 THEN 'upper(($1).' || quote_ident(attname)
 || ')::' || atttypid::regtype::text 
 ELSE '($1).' || quote_ident(attname)
 END AS fld
 FROM pg_catalog.pg_attribute
 WHERE attrelid = pg_typeof(NEW)::text::regclass
 AND attnum > 0
 AND attisdropped = FALSE
 ORDER BY attnum
 ), ',')
USING NEW
INTO NEW;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;

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

Trigger

CREATE TRIGGER trg_t_insbef
BEFORE INSERT
ON t -- works for any table
FOR EACH ROW
EXECUTE PROCEDURE trg_uc_on_insert();

SQL Fiddle.

Простой, неспецифический метод

Пока вы используете простые типы только в своих таблицах и хотите иметь в верхнем регистре все символьные данные, есть еще один грубый, быстрый и простой метод: переведите целую строку в text, в верхнем регистре текстовое представление, вернитесь к типу строки и обновите NEW с результатом. Подробная информация о типе строки таблицы

Функция триггера

CREATE OR REPLACE FUNCTION trg_uc_simple_on_insert()
 RETURNS trigger AS
$func$
BEGIN
EXECUTE 'SELECT ($1::' || pg_typeof(NEW) || ').*'
 USING upper(NEW::text)
 INTO NEW;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;

SQL Fiddle.

Мы должны разложить тип строки, потому что SELECT INTO назначает отдельные поля типа целевой строки один за другим. Мы не можем сразу назначить всю строку. Если вы посмотрите внимательно, "общее" решение делает то же самое, менее очевидное.

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

Кроме того, хотя код намного короче и проще, чем с помощью общего метода, это не обязательно быстрее, особенно с большим количеством незатронутых столбцов. Это, вероятно, намного быстрее в простых случаях.

licensed under cc by-sa 3.0 with attribution.