PostgreSQL. Генерация и вставка UUID в поле при добавлении строки. Timestamp добавления и обновления строки.

Дано: PostgreSQL PRO 11 (тот самый), работает в Alt Linux, конфигурация – кластер из одного master и двух slave.

Решение небольших типовых задач.

Генерация UUID. Есть пакеты, можно установить расширение. Но нет прав доступа. Пишем функцию. 

CREATE OR REPLACE FUNCTION sys_uuid_gen(OUT uuid character varying)
 RETURNS character varying
 LANGUAGE sql
AS $function$
SELECT (uuid_in(overlay(overlay(md5(random()::text || ':' || random()::text) placing '4' from 13) placing to_hex(floor(random()*(11-8+1) + 8)::int)::text from 17)::cstring)::varchar);
$function$
;

Вставка UUID в поле при добавлении строки. Триггер.

CREATE OR REPLACE FUNCTION f_ins_uuid()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF NEW.col_uuid IS NULL OR NEW.col_uuid = '' THEN
    NEW.col_uuid := (select sys_uuid_gen());
  END IF;
  RETURN NEW;
END;
$function$
;

-- f_ins_uuid имя самой функции 
-- col_uuid имя столбца для UUID
-- если при вызове функции не задано значение столбца col_uuid, ему будет присвоено значение select sys_uuid_gen()
-- sys_uuid_gen имя приведенной выше функции, возвращает UUID
-- функция универсальная, может использоваться в триггерах 
-- различных таблиц при наличии в них атрибута col_uuid

Таблица с триггером вставки UUID

-- создать таблицу (примитив, без ограничений и ПК)

CREATE TABLE t_dev_test_uuid (
	c1 varchar NULL,
	c2 varchar NULL,
	c3 varchar NULL,
	c4 varchar NULL,
	col_uuid varchar NULL
);

-- Table Triggers

create trigger trg_t_dev_test_uuid before
insert
    on
    t_dev_test_uuid for each row execute procedure f_ins_uuid();

-- при вставке в таблицу t_dev_test_uuid вызвать приведенную выше функцию f_ins_uuid
-- вызванная функция при отсутствии значения столбца col_uuid вызовет sys_uuid_gen и присвоит результат столбцу

Вставка и обновление поля при обновлении и вставке строки

Определим имена атрибутов.

i_timestamp – время вставки строки, тип timsetamp

u_timestamp – время обновления строки, тип timsetamp

-- функции для присвоения значений атрибутам i_timestamp и u_timestamp

CREATE OR REPLACE FUNCTION f_sys_i_timestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.i_timestamp = now(); 
   RETURN NEW;
END;
$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION f_sys_u_timestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.u_timestamp = now(); 
   RETURN NEW;
END;
$$ language 'plpgsql';

-- имя функции f_sys_u_timestamp_column 
-- значения атрибутам присваиваются принудительно, если они имеются, то будут переопределены

Таблица с триггером вставки timestamp

-- пусть будет таблица  (примитив, без ограничений и ПК) с двумя заранее определенными атрибутами


CREATE TABLE t_dev_test_timestamp (
	c1 varchar NULL,
	c2 varchar NULL,
	c3 varchar NULL,
	c4 varchar NULL,
	i_timestamp timestamp NOT NULL, -- атрибут timsetamp вставки строки 
	u_timestamp timestamp NULL,     -- атрибут timestamp обновления строки
);

-- Table Triggers

-- создадим триггер на обновление строки
create trigger trg_u_t_dev_test_timestamp before
update
    on
    t_dev_test_timestamp for each row execute procedure f_sys_u_timestamp_column();

-- создадим триггер на вставку строки
create trigger trg_i_t_dev_test_timestamp before
insert
    on
    t_dev_test_timestamp for each row execute procedure f_sys_i_timestamp_column();
Close Menu