spi — функционал/примеры интерфейса программирования сервера
Модуль spi предоставляет несколько рабочих примеров использования Интерфейса программирования сервера (Server Programming Interface, SPI) и триггеров. Хотя эти функции имеют некоторую ценность и сами по себе, они еще более полезны в качестве образцов, которые можно модифицировать под собственные нужды. Эти функции достаточно универсальны, чтобы применяться с любой таблицей, но при создании триггера следует явно указывать имена таблицы и полей (как описано ниже).
Каждая описанная ниже группа функций представлена в виде отдельно устанавливаемого расширения.
refint — функции для реализации ссылочной целостности
Функции check_primary_key() и check_foreign_key() используются для проверки ограничений внешних ключей. (Разумеется, эта функциональность уже давно вытеснена встроенным механизмом внешних ключей, но этот модуль по-прежнему полезен в качестве примера.)
Функция check_primary_key() проверяет ссылающуюся таблицу. Для ее применения создайте с ее помощью триггер BEFORE INSERT OR UPDATE для таблицы, ссылающейся на другую таблицу. Задайте в аргументах триггера: имена столбцов ссылающейся таблицы, образующих внешний ключ, имя ссылочной таблицы и имена столбцов в ней, образующих первичный/уникальный ключ. Чтобы управлять несколькими внешними ключами, создайте триггер для каждой такой ссылки.
Функция check_foreign_key() проверяет ссылочную таблицу. Для ее применения создайте с ее помощью триггер BEFORE DELETE OR UPDATE для таблицы, на которую ссылаются другие таблицы. Задайте в аргументах триггера: количество ссылающихся таблиц, для которых эта функция должна выполнить проверки, действие в случае обнаружения ссылающегося ключа (cascade — удалить ссылающуюся строку, restrict — прервать транзакцию, если ссылающийся ключ существует, setnull — установить в полях ссылающихся ключей значения NULL), имена столбцов ссылочной таблицы, образующих первичный/уникальный ключ, а затем имя ссылающейся таблицы и имена ее столбцов (повторить для стольких ссылающихся таблиц, сколько их указано в первом аргументе). Обратите внимание, что столбцы первичного/уникального ключа должны быть помечены как NOT NULL, и по ним должен быть создан уникальный индекс.
Пример:
-- Столбец ID таблицы A является первичным ключом:
CREATE TABLE A (
ID int4 not null
);
CREATE UNIQUE INDEX AI ON A (ID);
-- Столбцы REFB таблицы B и REFC таблицы C – внешние ключи, ссылающиеся на столбец ID таблицы A:
CREATE TABLE B (
REFB int4
);
CREATE INDEX BI ON B (REFB);
CREATE TABLE C (
REFC int4
);
CREATE INDEX CI ON C (REFC);
--Триггер для таблицы A:
CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
EXECUTE PROCEDURE
check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC');
/*
2 - означает, что проверку нужно провести для внешних ключей 2 таблиц.
cascade - определяет, что соответствующие ключи должны быть удалены.
ID - имя столбца первичного ключа в таблице с триггером (A). Можно
использовать столько столбцов, сколько потребуется.
B - имя (первой) таблицы с внешними ключами.
REFB - имя столбца внешнего ключа в этой таблице. Можно использовать столько
столбцов, сколько потребуется, но число столбцов ключа в ссылочной
таблице (A) меняться не должно.
C - имя второй таблицы с внешними ключами.
REFC - имя столбца внешнего ключа в этой таблице.
*/
--Триггер для таблицы B:
CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
EXECUTE PROCEDURE
check_primary_key ('REFB', 'A', 'ID');
/*
REFB - имя столбца внешнего ключа в таблице с триггером (B). Можно использовать
столько столбцов, сколько потребуется, но число столбцов ключа в ссылочной
таблице меняться не должно.
A - имя ссылочной таблицы.
ID - имя столбца первичного ключа в ссылочной таблице.
*/
--Триггер для таблицы C:
CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
EXECUTE PROCEDURE
check_primary_key ('REFC', 'A', 'ID');
-- Теперь попробуйте
INSERT INTO A VALUES (10);
INSERT INTO A VALUES (20);
INSERT INTO A VALUES (30);
INSERT INTO A VALUES (40);
INSERT INTO A VALUES (50);
INSERT INTO B VALUES (1); -- недопустимая ссылка
INSERT INTO B VALUES (10);
INSERT INTO B VALUES (30);
INSERT INTO B VALUES (30);
INSERT INTO C VALUES (11); -- недопустимая ссылка
INSERT INTO C VALUES (20);
INSERT INTO C VALUES (20);
INSERT INTO C VALUES (30);
DELETE FROM A WHERE ID = 10;
DELETE FROM A WHERE ID = 20;
DELETE FROM A WHERE ID = 30;
SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;
autoinc — функции для автоувеличения полей
Функция autoinc() является триггером, сохраняющим следующее значение последовательности в целочисленном поле. Это некоторым образом пересекается со встроенным функционалом столбцов типа serial, но все же не одно и то же: autoinc() будет переопределять попытки вставить другое значение поля при добавлении строк, а также при необходимости может использоваться для увеличения значения поля при изменениях.
Для применения этой функции создайте с ее помощью триггер BEFORE INSERT (или BEFORE INSERT OR UPDATE). Задайте в двух аргументах триггера имя целочисленного столбца, подлежащего изменению, и имя объекта последовательности, который будет поставлять значения. (На самом деле можно задать любое количество пар таких имен, если хотите изменять несколько автоувеличивающихся столбцов.)
Пример:
DROP SEQUENCE next_id;
DROP TABLE ids;
CREATE SEQUENCE next_id START -2 MINVALUE -2;
CREATE TABLE ids (
id int4,
idesc text
);
CREATE TRIGGER ids_nextid
BEFORE INSERT OR UPDATE ON ids
FOR EACH ROW
EXECUTE PROCEDURE autoinc (id, next_id);
INSERT INTO ids VALUES (0, 'first (-2 ?)');
INSERT INTO ids VALUES (null, 'second (-1 ?)');
INSERT INTO ids(idesc) VALUES ('third (1 ?!)');
SELECT * FROM ids;
UPDATE ids SET id = null, idesc = 'first: -2 --> 2'
WHERE idesc = 'first (-2 ?)';
UPDATE ids SET id = 0, idesc = 'second: -1 --> 3'
WHERE id = -1;
UPDATE ids SET id = 4, idesc = 'third: 1 --> 4'
WHERE id = 1;
SELECT * FROM ids;
SELECT 'Wasn''t it 4 ?' as nextval, nextval ('next_id') as value;
insert into ids (idesc) select textcat (idesc, '. Copy.') from ids;
SELECT * FROM ids;
insert_username — функции для отслеживания пользователя, меняющего таблицу
Функция insert_username() является триггером, сохраняющим имя текущего пользователя в текстовом поле. Это может быть полезно для отслеживания пользователя, последним изменившего конкретную строку в таблице.
Для применения этой функции создайте с ее помощью триггер BEFORE INSERT и/или UPDATE. Задайте в единственном аргументе триггера имя текстового столбца, подлежащего изменению.
Пример:
DROP TABLE username_test;
CREATE TABLE username_test (
name text,
username text not null
);
CREATE TRIGGER insert_usernames
BEFORE INSERT OR UPDATE ON username_test
FOR EACH ROW
EXECUTE PROCEDURE insert_username (username);
INSERT INTO username_test VALUES ('nothing');
INSERT INTO username_test VALUES ('null', null);
INSERT INTO username_test VALUES ('empty string', '');
INSERT INTO username_test VALUES ('space', ' ');
INSERT INTO username_test VALUES ('tab', ' ');
INSERT INTO username_test VALUES ('name', 'name');
SELECT * FROM username_test;
moddatetime — функции для отслеживания времени последнего изменения
Функция moddatetime() является триггером, сохраняющим текущее время в поле типа timestamp. Это может быть полезно для отслеживания времени последнего изменения конкретной строки в таблице.
Для применения этой функции создайте с ее помощью триггер BEFORE UPDATE. Задайте в единственном аргументе триггера имя столбца, подлежащего изменению. Столбец должен иметь тип timestamp или timestamp with time zone.
Пример:
DROP TABLE mdt;
CREATE TABLE mdt (
id int4,
idesc text,
moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE TRIGGER mdt_moddatetime
BEFORE UPDATE ON mdt
FOR EACH ROW
EXECUTE PROCEDURE moddatetime (moddate);
INSERT INTO mdt VALUES (1, 'first');
INSERT INTO mdt VALUES (2, 'second');
INSERT INTO mdt VALUES (3, 'third');
SELECT * FROM mdt;
UPDATE mdt SET id = 4
WHERE id = 1;
UPDATE mdt SET id = 5
WHERE id = 2;
UPDATE mdt SET id = 6
WHERE id = 3;
SELECT * FROM mdt;