Arrow
ArrowШілде 31, 2018, 2:57 Т.Қ.

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 , а сгруппированные.

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

  1. INSERT INTO new _table ( order , equip ment , p roduct , o quantity , time_sum )
  2. SELECT order_number , equipment , p roduct , SUM ( quantity ), SUM ( hours_count )
  3. FROM base _table GROUP BY order , equipment , p roduct
  4. 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” :

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

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

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

  1. CREATE TRIGGER select_to_table
  2. BEFORE INSERT OR UPDATE OR DELETE ON base _table
  3. 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 указывается имя самой вызываемой функции. Функция с этим именем должна быть описана перед объявлением или уже существовать, в противном случае это вызовет ошибку.

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

  1. CREATE OR REPLACE FUNCTION «имя функции»
  2. RETURNS TRIGGER AS
  3. $select_to$
  4. BEGIN
  5. -- тело функции --
  6.  
  7.  
  8.  
  9. RETURN NULL ; -- тип возвращаемого значения
  10. END ;
  11. $select_to$ LANGUAGE plpgsql;

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

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

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

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

  1. IF ( TG_OP = 'DELETE' ) THEN
  2.  
  3. -- операторы --
  4.  
  5. ELSEIF ( TG_OP = 'INSERT' ) THEN
  6.  
  7. -- операторы --
  8.  
  9. ELSIF ( TG_OP = 'UPDATE' ) THEN
  10.  
  11. -- операторы --
  12.  
  13. END IF ;

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

  1. IF ( TG_OP = 'DELETE' ) THEN
  2.  
  3. -- Удаление записи из таблицы new _table , которая удовлетворяет условию --
  4. DELETE FROM new _table WHERE order = OLD . order AND product = OLD . product AND equipment = OLD . equipment ;
  5. IF NOT FOUND THEN
  6.  
  7. RETURN NULL ; -- если строки в new _table не существует --
  8.  
  9. END IF ;
  10. RETURN OLD ;

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

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

  1. ELSEIF ( TG_OP = 'INSERT' ) THEN
  2.  
  3. -- Создание новой записи в таблице new _table - -
  4. 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 );
  5.  
  6. RETURN NEW ;

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

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

  1. ELSIF ( TG_OP = 'UPDATE' ) THEN
  2.  
  3. -- Обновление записи в new _table --
  4. UPDATE new _table SET order = NEW . order , equip ment = NEW . equipment , p roduct = NEW . p roduct , quantity = NEW . quantity , time_sum = NEW . hours_count
  5.  
  6. 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 ;
  7. RETURN NEW ;

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

  1. CREATE OR REPLACE FUNCTION «имя функции»
  2. RETURNS TRIGGER AS
  3. $select_to$
  4. BEGIN
  5. IF ( TG_OP = 'DELETE' ) THEN
  6.  
  7. -- Удаление записи из new _table , которая удовлетворяет условию
  8. DELETE FROM new _table WHERE order = OLD . order AND product = OLD . product AND equipment = OLD . equipment ;
  9. IF NOT FOUND THEN
  10.  
  11. RETURN NULL ; -- если строки в new _table не существует
  12.  
  13. END IF ;
  14. RETURN OLD ;
  15.  
  16. ELSEIF ( TG_OP = 'INSERT' ) THEN
  17.  
  18. -- C оздание новой записи в new _table
  19. INSERT INTO new _table ( order , equipme nt , p roduct , quantity , time_sum ) VALUES ( NEW . order_number , NEW . equipment ,
  20.  
  21. NEW . part , NEW . otk , NEW . hours_count );
  22. RETURN NEW ;
  23.  
  24. ELSIF ( TG_OP = 'UPDATE' ) THEN
  25.  
  26. -- Обновление записи в new _table
  27. UPDATE new _table SET order = NEW . order , equip ment = NEW . equipment , p roduct = NEW . p roduct ,
  28.  
  29. quantity = NEW . quantity , time_sum = NEW . hours_count
  30. WHERE order = OLD . order AND equip ment = OLD . equipment AND p roduct = OLD . p roduct AND
  31.  
  32. quantity = OLD . quantity AND time_sum = OLD . hours_count ;
  33. RETURN NEW ;
  34.  
  35. RETURN NULL ;
  36. END ;
  37. $select_to$ LANGUAGE plpgsql;
  38.  
  39.  
  40.  
  41. CREATE TRIGGER select_to_table
  42. BEFORE INSERT OR UPDATE OR DELETE ON base _table
  43. FOR ROW EXECUTE PROCEDURE process_select_to ();

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

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

Ол саған ұнайды ма? Әлеуметтік желілерде бөлісіңіз!

A
  • Там. 10, 2018, 7:40 Т.Қ.

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

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

Пікірлер

Тек рұқсаты бар пайдаланушылар ғана пікір қалдыра алады.
Кіріңіз немесе Тіркеліңіз
Ua

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

  • Нәтиже:84ұпай,
  • Бағалау ұпайлары4
Ua

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

  • Нәтиже:42ұпай,
  • Бағалау ұпайлары-8
ОК

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

  • Нәтиже:47ұпай,
  • Бағалау ұпайлары-6
Соңғы пікірлер
ИМ
Игорь МаксимовҚар. 22, 2024, 9:51 Т.Қ.
Django - Оқулық 017. Теңшелген Django кіру беті Добрый вечер Евгений! Я сделал себе авторизацию аналогичную вашей, все работает, кроме возврата к предидущей странице. Редеректит всегда на главную, хотя в логах сервера вижу запросы на правильн…
Evgenii Legotckoi
Evgenii LegotckoiҚаз. 31, 2024, 11:37 Т.Қ.
Django - Сабақ 064. Python Markdown кеңейтімін қалай жазуға болады Добрый день. Да, можно. Либо через такие же плагины, либо с постобработкой через python библиотеку Beautiful Soup
A
ALO1ZEҚаз. 19, 2024, 5:19 Т.Қ.
Qt Creator көмегімен fb3 файл оқу құралы Подскажите как это запустить? Я не шарю в программировании и кодинге. Скачал и установаил Qt, но куча ошибок выдается и не запустить. А очень надо fb3 переконвертировать в html
ИМ
Игорь МаксимовҚаз. 5, 2024, 4:51 Т.Қ.
Django - Сабақ 064. Python Markdown кеңейтімін қалай жазуға болады Приветствую Евгений! У меня вопрос. Можно ли вставлять свои классы в разметку редактора markdown? Допустим имея стандартную разметку: <ul> <li></li> <li></l…
d
dblas5Шілде 5, 2024, 8:02 Т.Қ.
QML - Сабақ 016. SQLite деректер қоры және онымен QML Qt-та жұмыс істеу Здравствуйте, возникает такая проблема (я новичок): ApplicationWindow неизвестный элемент. (М300) для TextField и Button аналогично. Могу предположить, что из-за более новой верси…
Енді форумда талқылаңыз
f
firstlunoxodАқп. 15, 2025, 1:46 Т.Қ.
Рисование на QGraphicsScene при зажатой кнопке мыши Подскажите, пожалуйста! Как данный класс можно дополнить, чтобы созданные объекты можно было перемещать мышкой по сцене?
Дмитрий
ДмитрийАқп. 3, 2025, 4:24 Т.Қ.
Создание deb-пакета. Как создать ярлык на рабочем столе после установки собственного deb-пакета? Всем привет. Сделал свой deb-пакет с программой. Всё устанавливается и работает. Ставлю по пути /usr/bin/my_application. Как для пользователя при установке пакета сразу создать ярлык на раб…
NW
Nayo WaiҚаң. 30, 2025, 7:22 Т.Қ.
не запускается компьютер!!! Не запускается компьютер (точнее работает блок , но сам монитор вообще жесть)В общем я ничего с интернета не скачивала в последнее время. На компе никаких левых пр…
n
nklyҚаң. 3, 2025, 12:52 Т.Қ.
Нужно запретить перемещение только некоторых итемов, остальные перемещать можно. Вопрос решен. Узнать QModelIndex элемента на который мы перетаскиваем другой элемент, можно с помощью функции indexAt(event->position().toPoint()) представления QTreeViev вызываемой в переопр…
M
MarselТам. 17, 2023, 12:26 Т.Ж.
OAuth2.0 через VK, получение email Спасибо большое за помощь и простите за то что отнял время своей невнимательностью.

Бізді әлеуметтік желілерде бақылаңыз