В процессе разработки одного приложения для учёта норм времени возникла необходимость в создании сводной таблицы с выборкой данных из уже имеющейся.
В качестве базы данных использовалась 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” .
Приветствую!