Счетчик номера версии

CREATE DATABASE plpgsql_triggers;

CREATE TABLE t(
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 
  s text, version INTEGER
);

CREATE OR REPLACE FUNCTION inc_version() RETURNS TRIGGER AS $$ 
BEGIN 
    IF TG_OP = 'INSERT' THEN 
        NEW.version := 1;
    ELSE 
        NEW.version := OLD.version + 1;
    END IF;
    RETURN NEW;
END 
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_inc_version 
BEFORE INSERT OR UPDATE ON t 
FOR EACH ROW EXECUTE FUNCTION inc_version();

INSERT INTO t(s) VALUES ('Раз');
SELECT * FROM t;
INSERT INTO t(s,version) VALUES ('Два',42);
SELECT * FROM t;
UPDATE t SET s = LOWER(s) WHERE id = 1;
SELECT * FROM t;
UPDATE t SET s = LOWER(s), version = 42 WHERE id = 2;
SELECT * FROM t;

-- Создаем последовательность
CREATE SEQUENCE t_version_seq START 1;
 
-- Создаем таблицу
CREATE TABLE t (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    s TEXT,
    version INTEGER DEFAULT NEXTVAL('t_version_seq')
);
 
-- Создаем функцию триггера
CREATE OR REPLACE FUNCTION update_version()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.version := 1;
    ELSIF TG_OP = 'UPDATE' THEN
        NEW.version := OLD.version + 1;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Создаем триггер
CREATE TRIGGER t_update_version
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW EXECUTE FUNCTION update_version();