изменения дорожки в postgresql
Я должен отслеживать изменения записей в таблице. То, что я сделал, составляют вторую таблицу, которая наследовалась сначала и добавляет счетчик пересмотра.
CREATE TABLE A (
id SERIAL,
foo TEXT,
PRIMARY KEY (id));
CREATE TABLE B (
revision INTEGER NOT NULL) INHERITS (A);
Затем я создал триггер, который обновит таблицу B каждый раз, когда A вставляется/обновляется. То, что я не могу выяснить, - то, как заставить B.revision сохранить отдельную "последовательность" для каждого идентификатора.
Пример: таблица A имеет 2 строки, меня и j.
я был обновлен 3 раза и должен иметь 3 изменения: (1, 2, 3).
j был обновлен 2 раза и должен иметь два изменения: (1, 2).
Вот то, что я имею до сих пор, возможно, я спускаюсь по неправильному пути, и кто-то может помочь мне!
CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
DECLARE
last_revision INTEGER;
BEGIN
SELECT INTO last_revision MAX(revision) FROM B WHERE id = NEW.id;
IF NOT FOUND THEN
last_revision := 0;
END IF;
INSERT INTO B SELECT NEW.*;
RETURN NEW;
END;
$table_update$ LANGUAGE plpgsql;
CREATE TRIGGER table_update
AFTER INSERT OR UPDATE ON A
FOR EACH ROW EXECUTE PROCEDURE table_update();
2 ответа
Если вам нужны номера версий только для упорядочивания и не обязательно, чтобы они были целыми числами, увеличивающимися на единицу для каждого идентификатора, самый простой способ сделать это - использовать последовательность для ревизии и позволить ей выполните отслеживание для вас:
CREATE TABLE A (
id SERIAL,
foo TEXT,
PRIMARY KEY (id)
);
CREATE TABLE B ( revision SERIAL NOT NULL) INHERITS (A);
CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
BEGIN
INSERT INTO B SELECT NEW.*;
RETURN NEW;
END;
$table_update$ LANGUAGE plpgsql;
CREATE TRIGGER table_update
AFTER INSERT OR UPDATE ON A
FOR EACH ROW EXECUTE PROCEDURE table_update();
Затем выполните вставки как обычно:
try=# insert into a (foo) values ('bar');
INSERT 0 1
try=# insert into a (foo) values ('bar');
INSERT 0 1
try=# update a set foo = 'you' where id = 1;
UPDATE 2
try=# select * from b;
id | foo | revision
----+-----+----------
2 | bar | 2
1 | you | 1
1 | you | 3
(3 rows)
Таким образом, вы можете получить все исправления для данной строки следующим образом:
try=# select * from b where id = 1 order by revision;
id | foo | revision
----+-----+----------
1 | you | 1
1 | you | 3
(2 rows)
Вот мое предложение:
CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
DECLARE
last_revision INTEGER;
BEGIN
SELECT INTO last_revision coalesce(MAX(revision), 0) FROM B WHERE id = NEW.id;
INSERT INTO B SELECT NEW.*, last_revision + 1;
RETURN NEW;
END;
$table_update$ LANGUAGE plpgsql;
Я изменил "if not found" на coalesce, который будет выбирать "0", если нет существующей ревизии. Затем я вставляю в B строку с увеличенной ревизией.
Будьте осторожны с наследованием: вам нужно будет использовать ключевое слово "only", чтобы ограничиться таблицей A при выборе и обновлении, как например:
select * from only A
update only A set foo = ... where id = ...