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

Таблица: 

=> CREATE TABLE t(
    id serial PRIMARY KEY,
    s text,
    version integer
);

CREATE TABLE

Триггерная функция: 

=> 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 FUNCTION

Триггер: 

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

CREATE TRIGGER

Проверяем: 

=> INSERT INTO t(s) VALUES ('Раз');

INSERT 0 1

=> SELECT * FROM t;

 id |  s  | version 
----+-----+---------
  1 | Раз |       1
(1 row)

Явное указание version игнорируется: 

=> INSERT INTO t(s,version) VALUES ('Два',42);

INSERT 0 1

=> SELECT * FROM t;

 id |  s  | version 
----+-----+---------
  1 | Раз |       1
  2 | Два |       1
(2 rows)

Изменение: 

=> UPDATE t SET s = lower(s) WHERE id = 1;

UPDATE 1

=> SELECT * FROM t;

 id |  s  | version 
----+-----+---------
  2 | Два |       1
  1 | раз |       2
(2 rows)

Явное указание также игнорируется: 

=> UPDATE t SET s = lower(s), version = 42 WHERE id = 2;

UPDATE 1

=> SELECT * FROM t;

 id |  s  | version 
----+-----+---------
  1 | раз |       2
  2 | два |       2
(2 rows)

Вставка и удаление

Сначала повторим пример из демонстрации: 

=> CREATE TABLE items(
    id serial PRIMARY KEY,
    description text NOT NULL,
    CONSTRAINT items_desc_unique UNIQUE(description)
);

CREATE TABLE

=> CREATE TABLE order_lines(
    id serial PRIMARY KEY,
    item_id integer NOT NULL REFERENCES items(id),
    qty integer NOT NULL
);

CREATE TABLE

=> CREATE VIEW order_lines_v AS
SELECT ol.id, i.description, ol.qty
FROM order_lines ol JOIN items i ON ol.item_id = i.id;

CREATE VIEW

=> CREATE OR REPLACE FUNCTION view_update() RETURNS trigger AS $$
BEGIN
    UPDATE order_lines
    SET item_id = (SELECT id FROM items WHERE description = NEW.description),
        qty = NEW.qty
    WHERE id = OLD.id;
    RETURN NEW;
EXCEPTION
    WHEN not_null_violation THEN
        RAISE EXCEPTION 'Указанной позиции "%" не существует',NEW.description;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

=> CREATE TRIGGER order_lines_v_upd
INSTEAD OF UPDATE ON order_lines_v
FOR EACH ROW EXECUTE PROCEDURE view_update();

CREATE TRIGGER

Добавим триггер для вставки: 

=> CREATE OR REPLACE FUNCTION view_insert() RETURNS trigger AS $$
BEGIN
    INSERT INTO order_lines(item_id, qty)
    SELECT (SELECT id FROM items WHERE description = NEW.description),
           NEW.qty;
    RETURN NEW;
EXCEPTION
    WHEN not_null_violation THEN
        RAISE EXCEPTION 'Указанной позиции "%" не существует',NEW.description;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

=> CREATE TRIGGER order_lines_v_ins
INSTEAD OF INSERT ON order_lines_v
FOR EACH ROW EXECUTE PROCEDURE view_insert();

CREATE TRIGGER

Проверим: 

=> INSERT INTO items(description) VALUES ('Болт');

INSERT 0 1

=> INSERT INTO items(description) VALUES ('Гайка');

INSERT 0 1

=> INSERT INTO items(description) VALUES ('Шуруп');

INSERT 0 1

=> INSERT INTO order_lines_v(description,qty) VALUES ('Гайка',50);

INSERT 0 1

=> INSERT INTO order_lines_v(description,qty) VALUES ('Болт',100);

INSERT 0 1

=> SELECT * FROM order_lines_v;

 id | description | qty 
----+-------------+-----
  1 | Гайка       |  50
  2 | Болт        | 100
(2 rows)

Добавим триггер для удаления: 

=> CREATE OR REPLACE FUNCTION view_delete() RETURNS trigger AS $$
BEGIN
    DELETE FROM order_lines
    WHERE id = OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

=> CREATE TRIGGER order_lines_v_del
INSTEAD OF DELETE ON order_lines_v
FOR EACH ROW EXECUTE PROCEDURE view_delete();

CREATE TRIGGER

Проверим: 

=> DELETE FROM order_lines_v;

DELETE 2

=> SELECT * FROM order_lines_v;

 id | description | qty 
----+-------------+-----
(0 rows)

Автоматическое создание позиции

Напишем вспомогательную функцию, которая возвращает идентификатор позиции по имени. Если позиции с указанным именем еще не существует, функция предварительно создаст ее. 

=> CREATE OR REPLACE FUNCTION get_item_id(description text) RETURNS integer AS $$
DECLARE
    id integer;
BEGIN
    SELECT i.id INTO id
    FROM items i
    WHERE i.description = get_item_id.description;

    IF NOT found THEN
        INSERT INTO items(description) VALUES (description)
        ON CONFLICT ON CONSTRAINT items_desc_unique
            DO UPDATE SET description = get_item_id.description
        RETURNING items.id INTO id;
    END IF;

    RETURN id;
END;
$$ VOLATILE LANGUAGE plpgsql;

CREATE FUNCTION

Можно обратить внимание на несколько моментов:

Теперь созданную функцию можно использовать в триггерах: 

=> CREATE OR REPLACE FUNCTION view_insert() RETURNS trigger AS $$
BEGIN
    INSERT INTO order_lines(item_id, qty)
    SELECT get_item_id(NEW.description),
           NEW.qty;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

=> CREATE OR REPLACE FUNCTION view_update() RETURNS trigger AS $$
BEGIN
    UPDATE order_lines
    SET item_id = get_item_id(NEW.description),
        qty = NEW.qty
    WHERE id = OLD.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

Проверим: 

=> INSERT INTO order_lines_v(description,qty) VALUES ('Винт',100);

INSERT 0 1

=> UPDATE order_lines_v SET description = 'Шайба';

UPDATE 1

=> SELECT * FROM items;

 id | description 
----+-------------
  1 | Болт
  2 | Гайка
  3 | Шуруп
  4 | Винт
  5 | Шайба
(5 rows)


=> SELECT * FROM order_lines;

 id | item_id | qty 
----+---------+-----
  3 |       5 | 100
(1 row)