Вставка NEW.* Из общего триггера с помощью EXECUTE в PL/pgsql

У меня есть несколько таблиц, которые используют функцию "Разделение"Postgres. Я хочу определить общий триггер BEFORE INSERT OF ROW для каждой таблицы, который будет 1) динамически создавать раздел в случае вставки в родительскую таблицу и 2) повторно выполнять вставку в разделе.

Что-то вроде:

CREATE OR REPLACE FUNCTION partition_insert_redirect( )
RETURNS trigger AS $BODY$
BEGIN
  ... create the new partition and set up the redirect Rules ...

  /* Redo the INSERT dynamically.  The new RULE will redirect it to the child table */
  EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) ||
          ' SELECT NEW.*'
END

Но "НОВАЯ" запись не видна внутри ИСПОЛНИТЕЛЬНОГО SQL. Как я могу сделать эту работу максимально простой?

В качестве альтернативы, можно ли как-то перебирать поля в новой записи?

Я думал об использовании временной таблицы:

EXECUTE 'CREATE TEMPORARY TABLE new_row (LIKE ' ||
        quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) ||
        ') ON COMMIT DROP';

INSERT INTO new_row SELECT NEW.*;

EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) ||
       ' SELECT * FROM new_row';
DROP TABLE new_row;

Но это также не работает из-за кешированной ссылки на временную таблицу: почему я получаю ошибки "связь с OID ##### не существует" при доступе к временным таблицам в функциях PL/PgSQL?

Я использую Postgres 8.2 и не могу перейти на любую другую версию.

РЕДАКТИРОВАТЬ:
Как отметил @alvherre, это возможно сделать в Postgres 8.4 с помощью синтаксиса EXECUTE ... USING. Смотрите пример на http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers

Источник

3 ответа

Решение

Мне удалось заставить это работать, динамически компилируя функцию, которая принимает строку NEW в качестве параметра:

    EXECUTE 'create or replace function partition_insert(r ' || TG_TABLE_NAME || ') RETURNS void AS $FUNC$' || 
            'BEGIN ' ||
                'insert into ' || TG_TABLE_NAME || ' SELECT r.*; ' ||
            'END $FUNC$ LANGUAGE plpgsql VOLATILE';
    PERFORM partition_insert(NEW);

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

Несмотря на то, что это уродливый клудж, похоже, это делает работу.

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

РЕДАКТИРОВАТЬ: Дополнительные морщины
В этой технике есть небольшая хитрость: если это действие EXECUTE/PERFORM откатывается с первой попытки из-за другой ошибки (например, в моем случае ошибка ограничения CHECK), то функция, содержащая этот код, похоже, кеширует ссылку на откатная функция partition_insert(), созданная с помощью EXECUTE, и последующие вызовы не выполняются из-за того, что кэшированный объект не найден.

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

Источник

Ты можешь использовать EXECUTE USING передать НОВЫЙ к нему. Ваш пример будет

EXECUTE 'INSERT INTO ' || TG_RELID || '::regclass SELECT $1' USING NEW;

(Обратите внимание, что я использую TG_RELID, приведенный для переклассификации, вместо того, чтобы возиться с TG_TABLE_SCHEMA и TABLE_NAME, потому что его проще использовать, если он нестандартный. Но тогда plpgsql в любом случае нестандартен.)

Источник

Да, вы можете использовать EXECUTE ... USING в 8.4. Например:

EXECUTE 'INSERT INTO ' || table_name || ' SELECT $1.*' USING NEW;

В более низких версиях (я проверял только в 8.3), вы можете использовать:

EXECUTE 'INSERT INTO ' || table_name ||
    ' SELECT (' || quote_literal(NEW) || '::' || TG_RELID::regclass || ').*';
Источник
Другие вопросы по тегам