INSERT с динамическим именем таблицы в триггерной функции

Я не уверен, как добиться чего-то вроде следующего:

CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$
    DECLARE
        shadowname varchar := TG_TABLE_NAME || 'shadow';
    BEGIN
        INSERT INTO shadowname VALUES(OLD.*);
        RETURN OLD;
    END;
$$
LANGUAGE plpgsql;

т.е. вставка значений в таблицу с динамически сгенерированным именем.
Выполнение приведенного выше кода дает:

ERROR:  relation "shadowname" does not exist
LINE 1: INSERT INTO shadowname VALUES(OLD.*)

Кажется, что переменные не расширяются/не разрешаются, как имена таблиц. Я не нашел ссылки на это в руководстве Postgres.

Я уже экспериментировал с EXECUTE следующим образом:

  EXECUTE 'INSERT INTO ' || quote_ident(shadowname) || ' VALUES ' || OLD.*;

Но не повезло:

ERROR:  syntax error at or near ","
LINE 1: INSERT INTO personenshadow VALUES (1,sven,,,)

Тип RECORD кажется потерянным: OLD.* похоже, преобразован в строку и получает репарацию, что приводит к разным типам проблем (например, значения NULL).

Любые идеи?

Ответы

Ответ 1

PostgreSQL 9.1 или более поздняя версия

format() имеет встроенный способ экранирования идентификаторов. Проще, чем раньше:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
   USING OLD;

   RETURN OLD;
END
$func$  LANGUAGE plpgsql;

Работает с выражением VALUES.

дБ <> скрипка здесь
Старый sqlfiddle.

Основные моменты

  • Используйте format() или quote_ident() для quote_ident() в кавычки идентификаторов (автоматически и только при необходимости), тем самым защищая от внедрения SQL-кода и простых нарушений синтаксиса.
    Это необходимо, даже с вашими именами таблиц!
  • Схема-квалифицировать имя таблицы. В зависимости от текущего параметра search_path пустое имя таблицы может в противном случае search_path в другую таблицу с тем же именем в другой схеме.
  • Используйте EXECUTE для динамических операторов DDL.
  • Передавайте значения безопасно с помощью предложения USING.
  • Обратитесь к прекрасному руководству по выполнению динамических команд в plpgsql.
  • Обратите внимание, что RETURN OLD; В триггере требуется функция для триггера BEFORE DELETE. Подробности в руководстве здесь.

Вы получаете сообщение об ошибке в вашей почти успешной версии, потому что OLD не виден внутри EXECUTE. И если вы хотите объединить отдельные значения разложенной строки, как вы пытались, вы должны подготовить текстовое представление каждого отдельного столбца с quote_literal() чтобы гарантировать правильный синтаксис. Вы также должны были бы знать имена столбцов заранее, чтобы обрабатывать их или запрашивать системные каталоги - что противоречит вашей идее иметь простую, динамическую функцию триггера...

Мое решение позволяет избежать всех этих осложнений. Также немного упростили.

PostgreSQL 9.0 или более ранняя версия

format() пока недоступен, поэтому:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger AS
$func$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;
END
$func$  LANGUAGE plpgsql;

Связанные с:

Ответ 2

Я просто наткнулся на это, потому что искал динамический INSTEAD OF DELETE триггер. В качестве благодарности за вопрос и ответы я опубликую свое решение для Postgres 9.3.

CREATE OR REPLACE FUNCTION set_deleted_instead_of_delete()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format('UPDATE %I set deleted = now() WHERE id = $1.id', TG_TABLE_NAME)
    USING OLD;
    RETURN NULL;
END;
$$ language plpgsql;