跟踪postgresql中的修订 [英] track revisions in postgresql

问题描述

我必须跟踪表中记录的修订.我要做的是创建第二个表，该表从第一个表继承并添加一个修订计数器.

I have to keep track of revisions of records in a table. What I've done is create a second table that inherits from the first and adds a revision counter.

CREATE TABLE A (
id SERIAL,
foo TEXT,
PRIMARY KEY (id));

CREATE TABLE B (
revision INTEGER NOT NULL) INHERITS (A);

然后，我创建了一个触发器，该触发器将在每次插入/更新A时更新表B.我不知道如何使B.revision为每个ID保留一个单独的序列".

Then I created a trigger that would update table B every time A is inserted/updated. What I can't figure out is how to make B.revision keep an individual "sequence" for each id.

示例:表A有2行，即i& j.
我已经更新了3次，应该进行3次修订:(1、2、3).
j已经更新了2次，应该进行两次修订:(1、2).

Example: table A has 2 rows, i & j.
i has been updated 3 times and should have 3 revisions: (1, 2, 3).
j has been updated 2 times and should have two revisions: (1, 2).

到目前为止，这就是我所拥有的，也许我走错了道路，有人可以帮助我！

Here is what I have so far, maybe I'm going down the wrong path and someone can help me!

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();

推荐答案

如果您只需要版本号用于订购，并且不需要专门为每个标识符加1的整数，这是最简单的方法要做的是使用修订序列，然后让它为您进行跟踪:

If you need the version numbers just for ordering, and don't specifically need them to be an integer that increase by one for each identifier, the easiest way to do it is to use a sequence for the revision and just let it do the tracking for you:

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();

然后像往常一样执行插入操作:

Then do the inserts as usual:

    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)

因此，您可以像这样获取给定行的所有修订:

So you can get all revisions for a given row like so:

    try=# select * from b where id = 1 order by revision;
     id | foo | revision 
    ----+-----+----------
      1 | you |        1
      1 | you |        3
    (2 rows)

这篇关于跟踪postgresql中的修订的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！