У процесі розробки одного додатка для обліку норм часу виникла потреба у створенні зведеної таблиці з вибіркою даних із вже існуючої.
Як база даних використовувалася PostgreSQL.
Вихідна таблиця “base_table” спрощено має такий вигляд:
Поля таблиці мають такі типи:
- id - ціле число, auto inc;
- дата - дата;
- кількість - ціле число;
- hours_count - час
Іншим полям спрощено надано тип 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” .
Приветствую!