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

Question

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

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 ценности).

Есть идеи?

Answer 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;

SQL Fiddle.
Работает с VALUES выражение, а также.

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

• Используйте формат () или quote_ident() при необходимости указывать идентификаторы и защищаться от SQL-инъекций.
  Это необходимо, даже с вашими именами таблиц!
• Схема-квалифицировать имя таблицы. В зависимости от текущего 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;

Связанные с:

• Как динамически использовать TG_TABLE_NAME в PostgreSQL 8.2?

Answer 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;