In the process of developing one application for accounting for time norms, it became necessary to create a pivot table with a selection of data from the existing one.
PostgreSQL was used as the database.
The source table base_table is simplified as follows:
Table fields have the following types:
- id - integer, auto inc;
- date - date;
- quantity - integer;
- hours_count - time
The rest of the fields are simply assigned the type text .
Simplifications are introduced so as not to focus on the structure of the table.
The following data must be selected from it into the "new_table" pivot table: order, equipment, product, SUM(quantity), SUM(hours_count) , the following fields must also be present in the pivot table data (integer ) and state (boolean) . In the new table, it is necessary to enter not just values from the order, equipment, product columns, but grouped ones.
For ease of understanding, looking ahead a little, I will give the proposed request code that satisfies the task:
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 );
Initially, there were thoughts about creating a separate table “new_table” as a summary table and in manual mode in a program using a database using an SQL query to select the necessary data from the main table. But still, the question remained how to be when creating new records in “base_table” after the selection is made, since the data will be entered continuously and they must be constantly updated in “new_table” .
After discussing at the EVILEG website forum possible options for solving the task, its creator proposed a way out of this situation by responding to changes in the data in the main table.
Based on this, it was decided that it was necessary to synchronize the data in the tables so that when creating, editing or deleting records in the “base_table” , the corresponding changes would also occur in the pivot table “new_table” .
Table view “new_table” :
It became clear that for this it is necessary to use trigger functions with the processing of events for creating, editing, and deleting data from the base table.
Let's pass to consideration of process of creation of such function with its description.
The following is the declaration of the trigger function:
CREATE TRIGGER select_to_table BEFORE INSERT OR UPDATE OR DELETE ON base _table FOR ROW EXECUTE PROCEDURE process_select_to ();
The first line contains a function declaration, let's call it "select_to_table" .
The second line indicates when performing what actions on the base table the function should be called. The BEFORE keyword means that the function must be called before creating a new record ( INSERT ), editing ( UPDATE ) or deleting an existing record ( DELETE ) in the “base_table” table.
Instead of BEFORE, it is possible to specify AFTER, which means calling the function after the above events occur.
It's worth noting that the official PostgreSQL documentation recommends using BEFORE as the keyword.
The third line specifies FOR ROW , or FOR EACH ROW . The first entry means a function call for one row, and the second - one for each row of the table.
And the last parameter after the PROCEDURE keyword is the name of the called function itself. A function with this name must be declared before the declaration or already exist, otherwise it will raise an error.
Now let's move on to the general view of the function itself.
CREATE OR REPLACE FUNCTION «имя функции» RETURNS TRIGGER AS $select_to$ BEGIN -- тело функции -- RETURN NULL ; -- тип возвращаемого значения END ; $select_to$ LANGUAGE plpgsql;
The function must have the above form with the obligatory indication of the return value. The return type is NULL because the trigger does not perform any actions on tables. All work on tables will take place in the body of the function.
The type of function should not raise any special questions. However, anyone interested in a more detailed description can refer to the official documentation.
Now let's move on to writing the body itself. Since we indicated earlier that we want to handle the INSERT , UPDATE , and DELETE events, we need to provide a response to each of them. To determine the type of operation in PostgreSQL, there is a variable TG_OP , which contains the name of the operation being performed.
Based on this, we will write a condition for checking the type of operation, which will have the following form:
IF ( TG_OP = 'DELETE' ) THEN -- операторы -- ELSEIF ( TG_OP = 'INSERT' ) THEN -- операторы -- ELSIF ( TG_OP = 'UPDATE' ) THEN -- операторы -- END IF ;
Now let's write the code to delete the entry:
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 ;
I think this query does not need a detailed description, except for one thing - this is the OLD keyword, which makes it possible to find out the old values of records from the “base_table” table, that is, before they are changed. It should also be noted that the return value of the delete records function must always be RETURN OLD .
Let's move on to creating a new entry:
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 ;
The request itself does not need a detailed description either. I will only say that using the word NEW it is possible to get new values of records from the “base_table” table, and also that as a return value when creating or editing records, it is necessary to specify NEW as a return value.
Now it's the turn to update the record:
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 ;
For this request, everything should be clear from the previous descriptions. Now it's time to look at the code in its entirety.
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 ();
After executing this code, a new trigger function will be registered in the PostgreSQL database to handle the creation, modification or deletion of records in “base_table”. Similar operations will be performed synchronously with the corresponding records in “new_table” .
Приветствую!