I have a pessoa table. In it I have a field called pe_modified that aims to have the date of the last modification in the table.
Instead of having to refresh in the query, is it possible to be automatic? Is there any type of field or a default value that updates the time every time an update is performed on the table?
Good afternoon.
To update fields before or after events, you need to create a trigger.
Below is an example of a trigger:
CREATE OR REPLACE FUNCTION trg_update_conhecimento()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO conta_corrente
(descricao, data, fg_debito, valor, numero)
VALUES
('CONHECIMENTO GERADO', CURRENT_DATE, true, NEW.frete_peso + NEW.frete_km, NEW.numero);
RETURN NEW;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER trigger_update_conhecimento
AFTER UPDATE
ON conhecimento
FOR EACH ROW
EXECUTE PROCEDURE trg_update_conhecimento();
The above trigger makes an insert in the current_account table after executing an update in the knowledge table.
The now() function returns the current date. One way to automate this mechanism is to use a trigger as the code below:
CREATE OR REPLACE FUNCTION update_pe_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.pe_modified = now();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_pessoa_pe_modified
BEFORE UPDATE ON pessoa
FOR EACH ROW EXECUTE PROCEDURE
update_pe_modified_column();
Source: Automatically updating a timestamp column in PostgreSQL