Update date at each update in a table

Asked

Viewed 720 times

1

I have a table pessoa. In it I have a field called pe_modified which aims to have the date of the last modification in the table.

Instead of me having to update on the query, is it possible to be automatic ? Is there any kind of field or value default that updates the time whenever a table update is performed?

postgresql timestamp

  • Have you ever seen triggers? See: https://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html

2 answers

1

The function now() returns the current date. One way to automate this mechanism is by using 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

0

Good afternoon.

To update fields before or after events, you need to create a Trigger.

Below an example of 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 does an Insert in the current count table after an update to the knowledge table.

  • I think this answer would be more useful if you gave an example referring to the tables/columns cited by @Jonathan.

Browser other questions tagged postgresql timestamp

You are not signed in. Login or sign up in order to post.