(Примечание: обновлено с принятым ответом ниже.)
Для разделенной таблицы PostgreSQL 8.1 (или более поздней), как определить триггер UPDATE
и процедуру для "перемещения" записи из одного раздела в другой, если UPDATE
подразумевает изменение в ограниченном поле который определяет сегрегацию раздела?
Например, у меня есть записи таблицы, разделенные на активные и неактивные записи:
create table RECORDS (RECORD varchar(64) not null, ACTIVE boolean default true);
create table ACTIVE_RECORDS ( check (ACTIVE) ) inherits RECORDS;
create table INACTIVE_RECORDS ( check (not ACTIVE) ) inherits RECORDS;
Триггер и функция INSERT
работают хорошо: новые активные записи попадают в одну таблицу, а новые неактивные записи - в другую. Я хотел бы, чтобы UPDATE
в поле ACTIVE "переместил" запись из одной таблицы потомков в другую, но обнаружил ошибку, которая предполагает, что это может быть невозможно.
Спецификация триггера и сообщение об ошибке:
pg=> CREATE OR REPLACE FUNCTION record_update()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.active = OLD.active) THEN
RETURN NEW;
ELSIF (NEW.active) THEN
INSERT INTO active_records VALUES (NEW.*);
DELETE FROM inactive_records WHERE record = NEW.record;
ELSE
INSERT INTO inactive_records VALUES (NEW.*);
DELETE FROM active_records WHERE record = NEW.record;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
pg=> CREATE TRIGGER record_update_trigger
BEFORE UPDATE ON records
FOR EACH ROW EXECUTE PROCEDURE record_update();
pg=> select * from RECORDS;
record | active
--------+--------
foo | t -- 'foo' record actually in table ACTIVE_RECORDS
bar | f -- 'bar' record actually in table INACTIVE_RECORDS
(2 rows)
pg=> update RECORDS set ACTIVE = false where RECORD = 'foo';
ERROR: new row for relation "active_records" violates check constraint "active_records_active_check"
Воспроизведение с помощью триггерной процедуры (возврат NULL и т.д.) предлагает мне, чтобы ограничение было проверено, и ошибка была поднята до вызова триггера, что означает, что мой текущий подход не будет работать. Может ли это работать?
ОБНОВЛЕНИЕ/ОТВЕТ
Ниже приведена процедура запуска UPDATE
, с которой я закончил использование той же процедуры, назначенной каждому из разделов. Кредит полностью соответствует Bell, ответ на который дал мне ключевое понимание для запуска на разделах:
CREATE OR REPLACE FUNCTION record_update()
RETURNS TRIGGER AS $$
BEGIN
IF ( (TG_TABLE_NAME = 'active_records' AND NOT NEW.active)
OR
(TG_TABLE_NAME = 'inactive_records' AND NEW.active) ) THEN
DELETE FROM records WHERE record = NEW.record;
INSERT INTO records VALUES (NEW.*);
RETURN NULL;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;