Политика конфиденциальностиКонтактыО сайтеОтзывыGitHubDonate
© EVILEG 2015-2018
Рекомендует хостинг
TIMEWEB

Работа с триггерными функциями в PostgreSQL

PostgreSQL, Триггерные функции, База данных

В процессе разработки одного приложения для учёта норм времени возникла необходимость в создании сводной таблицы с выборкой данных из уже имеющейся.

В качестве базы данных использовалась PostgreSQL.

Исходная таблица “ base_table ” упрощенно имеет следующий вид:

Поля таблицы имеют следующие типы:

  • id - integer, auto inc;
  • date - date;
  • quantity - integer;
  • hours_count - time

Остальным полям упрощенно присвоен тип text .

Упрощения введены чтобы не заострять внимание на структуре таблицы.

Из неё в сводную таблицу «new_table» необходимо выбрать следующие данные: order, equipment, product, SUM(quantity), SUM(hours_count) , также в сводной таблице должны присутствовать следующие поля data (integer) и state (boolean) . В новую таблицу необходимо вносить не просто значения из столбцов order, equipment, product , а сгруппированные.

Для простоты понимания, забегая немного наперед, приведу предполагаемый код запроса удовлетворяющий поставленной задаче:

INSERT INTO new _table ( order , equip ment , p roduct , o quantity , time_sum )
SELECT order_number , equipment , p roduct , SUM ( quantity ), SUM ( hours_count )
FROM base _table GROUP BY order , equipment , p roduct
HAVING NOT EXISTS ( SELECT order , equip ment , p roduct FROM new _table WHERE order = order AND equip ment = equipment AND p roduct = p roduct );

Первоначально были мысли по поводу создания отдельной таблицы “new_table” в качестве сводной и в ручном режиме в программе использующей базу данных при помощи SQL запроса совершать выборку необходимых данных из основной таблицы. Но все же оставался вопрос как быть при создании новых записей в “base_table” после осуществления выборки, так как данные будут вноситься непрерывно и их необходимо постоянно обновлять и в “new_table” .

После обсуждения на форуме сайта EVILEG возможных вариантов решения поставленной задачи, его создателем был предложен вариант выхода из сложившейся ситуации путём реакции на изменения данных в основной таблице.

Исходя из этого было принято решение о необходимости синхронизации данных в таблицах так, чтобы при создании, редактировании или удалении записей в “base_table” соответствующие изменения происходили и в сводной таблице “new_table” .

Вид таблицы “new_table” :

Стало ясно, что для этого необходимо использовать триггерные функции с обработкой событий создания, редактирования и удаления данных из базовой таблицы.

Перейдём к рассмотрению процесса создания такой функции с её описанием.

Ниже указано объявление триггерной функции:

CREATE TRIGGER select_to_table
BEFORE INSERT OR UPDATE OR DELETE ON base _table
FOR ROW EXECUTE PROCEDURE process_select_to ();

В первой строке указано объявление функции, назовём её «select_to_table» .

Во второй строке указанно при выполнении каких действий над базовой таблицей функция должна быть вызвана. Ключевое слово BEFORE означает, что функцию необходимо вызывать до создания новой записи ( INSERT ), редактирования ( UPDATE ) или удалении существующей записи ( DELETE ) в таблице “base_table” .

Вместо BEFORE возможно указывать AFTER, что значит вызов функции после наступления выше указанных событий.

Стоит отметить, что официальная документация PostgreSQL рекомендует, использовать в качестве ключевого слова BEFORE .

В третьей строке указывается FOR ROW , или FOR EACH ROW . Первая запись означает вызов функции для одной строки, а вторая - по очерёдный для каждой строки таблицы.

И последним параметром после ключевого слова PROCEDURE указывается имя самой вызываемой функции. Функция с этим именем должна быть описана перед объявлением или уже существовать, в противном случае это вызовет ошибку.

Теперь перейдём к общему виду самой функции.

CREATE OR REPLACE FUNCTION «имя функции»
RETURNS TRIGGER AS
$select_to$
BEGIN
-- тело функции --



RETURN NULL ; -- тип возвращаемого значения
END ;
$select_to$ LANGUAGE plpgsql;

Функция должна иметь выше приведенный вид с обязательным указанием возвращаемого значения. В качестве типа возвращаемого значения указан NULL , так как триггер не выполняет никаких действий над таблицами. Вся работа над таблицами будет происходить в теле функции.

Вид функции особых вопросов вызывать не должен. Тем не менее все заинтересованные в более подробном описании могут обратиться к официальной документации.

Теперь перейдём к написанию самого тела. Так как мы указали ранее, что хотим обрабатывать события INSERT , UPDATE и DELETE нам необходимо предусмотреть реакцию на каждое из них. Для определения типа операции в PostgreSQL существует переменная TG_OP , в которой и содержится имя выполняемой операции.

Исходя из этого напишем условие проверки типа операции, которое будет иметь следующий вид:

IF ( TG_OP = 'DELETE' ) THEN

-- операторы --

ELSEIF ( TG_OP = 'INSERT' ) THEN

-- операторы --

ELSIF ( TG_OP = 'UPDATE' ) THEN

-- операторы --

END IF ;

Теперь напишем код для удаления записи:

IF ( TG_OP = 'DELETE' ) THEN

-- Удаление записи из таблицы new _table , которая удовлетворяет условию --
DELETE FROM new _table WHERE order = OLD . order AND product = OLD . product AND equipment = OLD . equipment ;
IF NOT FOUND THEN

RETURN NULL ; -- если строки в new _table не существует --

END IF ;
RETURN OLD ;

Думаю данный запрос в подробном описании не нуждается, кроме одного момента - это ключевое слово OLD , которое даёт возможность узнать старые значения записей из таблицы “base_table” , то есть до их изменения. Также необходимо отметить, что в функции удаления записей в качестве возвращаемо значения всегда должно быть указано RETURN OLD .

Перейдём к созданию новой записи:

ELSEIF ( TG_OP = 'INSERT' ) THEN

-- Создание новой записи в таблице new _table - -
INSERT INTO new _table ( order , equipme nt , p roduct , quantity , time_sum VALUES ( NEW . order_number , NEW . equipment , NEW . part , NEW . otk , NEW . hours_count );

RETURN NEW ;

Сам запрос в подробном описании также не нуждается. Скажу только, что используя слово NEW возможно получить новые значения записей из таблицы “base_table” , а также, что в качестве возвращаемого значения при создании или редактировании записей необходимо указывать в качестве возвращаемого значения NEW .

Теперь пришла очередь для обновления записи:

ELSIF ( TG_OP = 'UPDATE' ) THEN

-- Обновление записи в new _table --
UPDATE new _table SET order = NEW . order , equip ment = NEW . equipment , p roduct = NEW . p roduct , quantity = NEW . quantity , time_sum = NEW . hours_count

WHERE order = OLD . order AND equip ment = OLD . equipment AND p roduct = OLD . p roduct AND quantity = OLD . quantity AND time_sum = OLD . hours_count ;
RETURN NEW ;

По данному запросу все должно быть понятно из предыдущих описаний. Теперь пришло время посмотреть на код целиком.

CREATE OR REPLACE FUNCTION «имя функции»
RETURNS TRIGGER AS
$select_to$
BEGIN
IF ( TG_OP = 'DELETE' ) THEN

-- Удаление записи из new _table , которая удовлетворяет условию
DELETE FROM new _table WHERE order = OLD . order AND product = OLD . product AND equipment = OLD . equipment ;
IF NOT FOUND THEN

RETURN NULL ; -- если строки в new _table не существует

END IF ;
RETURN OLD ;

ELSEIF ( TG_OP = 'INSERT' ) THEN

-- C оздание новой записи в new _table
INSERT INTO new _table ( order , equipme nt , p roduct , quantity , time_sum ) VALUES ( NEW . order_number , NEW . equipment ,

NEW . part , NEW . otk , NEW . hours_count );
RETURN NEW ;

ELSIF ( TG_OP = 'UPDATE' ) THEN

-- Обновление записи в new _table
UPDATE new _table SET order = NEW . order , equip ment = NEW . equipment , p roduct = NEW . p roduct ,

quantity = NEW . quantity , time_sum = NEW . hours_count
WHERE order = OLD . order AND equip ment = OLD . equipment AND p roduct = OLD . p roduct AND

quantity = OLD . quantity AND time_sum = OLD . hours_count ;
RETURN NEW ;

RETURN NULL ;
END ;
$select_to$ LANGUAGE plpgsql;



CREATE TRIGGER select_to_table
BEFORE INSERT OR UPDATE OR DELETE ON base _table
FOR ROW EXECUTE PROCEDURE process_select_to ();

После выполнения данного кода в базе данных PostgreSQL будет зарегистрирована новая триггерная функция для обработки создания, изменения или удаления записей в “base_table”. Синхронно аналогичные операции будут выполняться и с соответствующими записями в “new_table” .

Рекомендуем хостинг TIMEWEB
Рекомендуем хостинг TIMEWEB
Стабильный хостинг, на котором располагается социальная сеть EVILEG. Для проектов на Django рекомендуем VDS хостинг.
A
  • #
  • 10 августа 2018 г. 13:40

Приветствую!

Если вы создаете новую таблицу, почему бы просто не сделать вьюху ? Просто от одного названия "триггер" как-то не хочется его использовать, а уж кода сколько писать...

Комментарии

Только авторизованные пользователи могут публиковать комментарии.
Пожалуйста, авторизуйтесь или зарегистрируйтесь
24 марта 2019 г. 13:24
Дмитрий

Qt - Тест 001. Сигналы и слоты

  • Результат:68баллов,
  • Очки рейтинга-1
l
24 марта 2019 г. 11:39
laxy93

C++ - Тест 001. Первая программа и типы данных

  • Результат:86баллов,
  • Очки рейтинга6
l
24 марта 2019 г. 11:32
laxy93

Qt - Тест 001. Сигналы и слоты

  • Результат:78баллов,
  • Очки рейтинга2
Последние комментарии
22 марта 2019 г. 12:32
Евгений Легоцкой

Ну может бибилотеки не те положили? У вас сборка для MinGW, а либы для MSVC.
ВН
22 марта 2019 г. 11:08
Волчонок Над-Пропастью

Из кьюта приложение не хочет запускаться, аварийно завершается, но каких-либо ошибок не выдаёт. Оно открывается, после нажатия "старт" зависает и завершается. Если не из кьюта запускать дебаг ...
21 марта 2019 г. 15:49
Евгений Легоцкой

серьезно? Эта ошибка 404 уже даже не относится к данному вопросу. В каких-то urls в вашем проекте. Вопросы, которые не относятся непосредственно к статье, задавайте, пожалуйста на...
АБ
21 марта 2019 г. 15:00
Артем Бел

а это где? сейчас ошибка в debag такая: Page not found (404)Request Method: GETRequest URL: http://amadowshop.site/accounts/profile/
21 марта 2019 г. 12:47
Евгений Легоцкой

не вижу здесь проблемы, поменяйте url для callback в приложения oauth в социальных сетях https://amadowshop.site/auth/verify/complete/facebook/
Сейчас обсуждают на форуме
m
24 марта 2019 г. 10:36
monevich

Отвечу на свой же вопрос, может кому то это пригодится. Да, можно в функции main использовать такую конструкцию. При запуске программы из Qt передаю свой аргумент в параметрах командной строк...
22 марта 2019 г. 12:29
Дмитрий

Да, мьютекс добавил, но в том потоке, где сигнал вызывается.
ВН
22 марта 2019 г. 9:04
Волчонок Над-Пропастью

Да, на эту проблему! Зато у меня теперь новая, ругается на подключение QMediaPlayer, пишет, что не находит подобного файла или библиотеки. Пока не нашла как исправить, только вариант с переуст...
22 марта 2019 г. 8:58
Михаиллл

4) это , похоже, для создания видоизмененных страниц, для анимации. 3,5) добавить фвйл/qt/ui.qml
22 марта 2019 г. 8:04
Евгений Легоцкой

Привет. Если там std::vector , то наверное можно заранее подготовить вектор с необходимым количеством элементов и просто устанавливать значение по индексу элемента. Но в данном слу...
Присоединяйтесь к нам в социальных сетях

Для зарегистрированных пользователей на сайте присутствует минимальное количество рекламы