The example below targets my tabusuarios table from my time database.
My time database has 13 MB.
The purpose here is to delete the oldest record from the user table if the database exceeds a given value.
The value I'm going to arbitrate is 10 MB.
First, a function ( TRIGGER FUNCTION ), via the PGAdmin console.
Uma TRIGGER FUNCTION é o que guarda o mecanismo de execução de algum procedimento, uma instrução SQL normalmente.
As we will see, within the FUNCTION TRIGGER is a set of instructions that effectively does something productive in the database.
In your case, the procedure is deletion.
The function below asks the size of my database. If it is greater than 10 MB (10 000 000 bytes), have it execute a SQL to delete the record that has the smallest (auto_increment) ID, which of course is the oldest.
It only allows deleting 1 record, because of the LIMIT 1 clause.
In the PGAdmin console:
CREATE OR REPLACE FUNCTION
fun_deletaregistrousuarios()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF(SELECT pg_database_size ('tempo') >
'10000000') THEN
DELETE FROM tabusuarios WHERE id = (SELECT id
tabusuarios FROM tabusuarios ORDER BY id LIMIT 1);
END IF;
RETURN NULL;
END;
$$;
Um TRIGGER é um bloco de instruções pelo qual se vigia um evento qualquer numa tabela, disparando uma FUNCTION TRIGGER. Funciona como um despertador, que toca a campainha numa hora determinada. No nosso caso, vai 'despertar a função' caso alguma coisa aconteça com os dados da tabela.
In the PGAdmin console:
CREATE TRIGGER tg_vigia_usuarios AFTER INSERT
ON tabusuarios
FOR EACH STATEMENT EXECUTE PROCEDURE
fun_deletaregistrousuarios();
In our case, the event is AFTER INSERT , that is, if there is a single record that is included in my user table, it will trigger the mechanism.
O *trigger* é associado à tabela, porque é sobre ela que se vigia qualquer inserção.
Out of curiosity, in my schema public , I can see a new entry in the 'Function Triggers' folder, named fun_deletareusername p>
Also, within the same schema , now in the tables folder, in the tab tab , which is Triggers . There, I find tg_vigia_usuarios .
Por fim, estando tudo pronto, no momento da inserção de um novo registro na tabela tabusuarios, um outro mais antigo foi deletado, uma vez que meu banco de dados tem 13 MB e o limite para o trigger provocar a função é de 10 MB.
In other words, since my database (13 MB) already exceeds in size the test value of the IF block of my function - that is triggered by the trigger if the database is greater than 10 MB - the DELETE statement is executed in an old record.
Of course, my approach does not exactly solve the proposed problem because it deletes only one record and it may be insufficient to bring the size of the database to the desired boundary, but this is an adjustment in the TRIGGER FUNCTION.
As a suggestion, loop in the deletion or another way to span more records and test after each deletion the size of the database may be an output.
It's just a way to start.
Set the values - such as the name and SQL statement of the function's core and the event in the trigger - to any other requirement.
