Динамический доступ к значению столбца в записи

Можно ли динамически получить доступ к значению столбца из записи по его имени?

Я пишу триггерную функцию, которая выполняет динамическую команду SQL, и я хотел бы динамически извлекать значение столбца из записи NEW по имени столбца.

Вот упрощенный пример того, что я пытаюсь сделать:

$$
DECLARE
   command text := 'UPDATE $1 SET $2 = $3';
   myColumn := 'votes'
BEGIN
   EXECUTE command using 'anotherTable', myColumn, NEW.myColumn;
END
$$

postgresql triggers dynamic-sql plpgsql procedures
person Juan Jose Lenero Lozano    schedule 09.09.2015    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Это возможно, но предложение USING в EXECUTE может передавать только значения.
В то время как идентификаторы подобные имена таблиц и столбцов должны быть объединены (остерегайтесь SQL-инъекций!) перед выполнением команды. Использование format()< /a> (Postgres 9.1+) это может работать так:

$$
DECLARE
   _command text := 'UPDATE %I SET %I = $1 WHERE ....'; -- add WHERE condition
   _col text := 'votes';
BEGIN
   EXECUTE format(_command, 'anotherTable', _col)
   USING  NEW.myColumn;
END
$$;

Исправлена ​​пара мелких проблем при прохождении.

Следует отметить, что NEW доступен только в триггерных функциях.

Имейте в виду, что 'anotherTable' здесь чувствительно к регистру (преобразуется и экранируется из строки), а NEW.myColumn — нет (обрабатывается как идентификатор). Используйте допустимые идентификаторы в нижнем регистре без кавычек в Postgres, чтобы упростить себе жизнь.

Связанные ответы с дополнительным объяснением и ссылками:

Чтобы динамически извлечь значение столбца из записи NEW по имени столбца.

... вы можете использовать hstore #= оператор:

Или вы можете заставить его работать со стандартными функциями динамического SQL:

$$
DECLARE
   _col text := 'votes';
   _new_col text := 'column_name_in_new';  -- enter column name here, case sensitive
BEGIN
   EXECUTE format(
       'UPDATE %I SET %I = $1.%I WHERE ... '  -- add WHERE condition
     , 'anotherTable', _col, _new_col)
   USING  NEW;  -- pass whole row
END
$$;

Связанный:

person Erwin Brandstetter    schedule 09.09.2015
comment
Спасибо за отличный ответ, однако это все еще не совсем то, что я пытался сделать. В вашем примере вы явно обращались к NEW.myColumn;. Я хотел бы получить динамический доступ к столбцу на основе ранее существовавшего значения, например NEW[_column]. Кто-то предложил в другом месте использовать hstore. Это работает: hstore(NEW)->_column для динамического доступа к столбцу votes в NEW. Однако я совсем не знаком с hstore и предпочел бы не включать расширение, если есть более простая альтернатива. - person Juan Jose Lenero Lozano; 10.09.2015
comment
@RedHusky: я добавил для этого еще одно решение. - person Erwin Brandstetter; 10.09.2015