Как получить доступ к новому или старому полю с указанием только имени поля?

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

Например, у меня есть следующая схема:

CREATE TABLE daily_reports(
     start_on date
   , show_id uuid
   , primary key(start_on, show_id)

     -- _graph are hourly values, while _count is total for the report
   , impressions_count bigint not null
   , impressions_graph bigint[] not null

   -- interactions_count, interactions_graph
   -- twitter_interactions_count, twitter_interactions_graph
);

Проверка должна подтвердить, что impressions_count = sum(impressions_graph).

Я застрял, потому что не знаю, как динамически получить доступ к полю из NEW из plpgsql:

CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
DECLARE
  total bigint;
  array_sum bigint;
BEGIN
  -- TG_NARGS = 2
  -- TG_ARGV[0] = 'impressions_count'
  -- TG_ARGV[1] = 'impressions_graph'

  -- How to access impressions_count and impressions_graph from NEW?
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_daily_reports_impressions
ON daily_reports BEFORE INSERT OR UPDATE
FOR EACH ROW EXECUTE
  validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');

Я попробовал Выполнение динамических команд , выполнив EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0], но PL /PGsql жалуется, что NEW - неизвестное отношение.

Я специально настраиваю PostgreSQL 9.1.

6 голосов | спросил François Beausoleil 19 MarpmWed, 19 Mar 2014 23:31:35 +04002014-03-19T23:31:35+04:0011 2014, 23:31:35

1 ответ


12

Собственно, поскольку NEW - это хорошо определенный составной тип, вы можете просто получить доступ к любому столбцу с простой и простой атрибутикой. Сам SQL не разрешает динамические идентификаторы (имена таблиц или столбцов и т. Д.). Но вы можете использовать динамический SQL с ---- +: = 1 =: + ---- в функции PL /pgSQL.

Demo

EXECUTE

Приведение к CREATE OR REPLACE FUNCTION trg_demo1() RETURNS TRIGGER AS $func$ DECLARE _col_value text; _col_name text := quote_ident(TG_ARGV[0]); -- escape identifier BEGIN EXECUTE format('SELECT ($1).%s::text', _col_name) USING NEW INTO _col_value; -- do something with _col_value ... RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', _col_name, _col_value; RETURN NEW; END $func$ LANGUAGE plpgsql; является необязательным. Использование этого, потому что оно работает повсеместно. Если вы знаете тип, вы можете работать без кастинга ...

Использование text с помощью format(), потому что идентификатор уже сбежал в этой точке.
В противном случае используйте %s с помощью format() для защиты против SQL-инъекции.

Альтернативно , в Postgres 9.3 или новее вы можете преобразовать %I в JSON с NEW и доступ к столбцам в качестве ключей:

to_json()

Поскольку имя столбца не конкатенируется в строку SQL, SQL-инъекция невозможна, и имя не нужно экранировать.

dbfiddle здесь (с ---- +: = 11 =: + ---- вместо CREATE OR REPLACE FUNCTION trg_demo2() RETURNS TRIGGER AS $func$ DECLARE _col_value text := to_json(NEW) ->> TG_ARGV[0]; -- no need to escape identifier BEGIN RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', TG_ARGV[0], _col_value; RETURN NEW; END $func$ LANGUAGE plpgsql; , чтобы эффект был видимым ).

по теме:

ответил Erwin Brandstetter 20 MaramThu, 20 Mar 2014 06:21:03 +04002014-03-20T06:21:03+04:0006 2014, 06:21:03

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132