postgresql - trigger, update timestamp on field update

Question

BACKGROUND: we have a completed_flag that is default 0 and updates to 1 when a person completes a survey. I want to record the timestamp this update happens

After writing this trigger/function to update a timestamp when a flag gets triggered from 0 to 1, I am questioning whether I did this the best way. I suspect it is not the best way to do this but I am somewhat new to postgres. Can the trigger call the function only when that value changes? That would eliminate the need for checking this value every ROW update, which seems excessive

-- add new column to store time the respondent completed
ALTER TABLE  tbl_pXXXX ADD COLUMN complete_time timestamp without time zone;


-- function to insert timestamp
CREATE FUNCTION completed_timestamp() RETURNS trigger AS $$
   LANGUAGE plpgsql
BEGIN
 IF new.complete_flag = 1 and old.completed_flag = 0
            THEN UPDATE tbl_pXXXX
            SET complete_time = current_timestamp
END;
$$;

-- trigger to call the function
CREATE TRIGGER update_timestamp
AFTER UPDATE ON tbl_pXXXX
FOR EACH ROW 
EXECUTE PROCEDURE completed_timestamp();

Nick Barnes · Accepted Answer · 2015-07-01 00:53:37Z

1

As long as you're only making changes to the row which fired the trigger, it's far simpler (and cheaper) to use a BEFORE UPDATE trigger, which can make changes to new.complete_flag before it's saved to the table, rather than having to run an UPDATE statement after it's already in there.

You can also limit the trigger to updates of a particular field with the UPDATE OF <column> clause of the CREATE TRIGGER statement.

The end result would look something like this:

CREATE FUNCTION completed_timestamp() RETURNS trigger AS $$
   LANGUAGE plpgsql
BEGIN
  IF new.complete_flag = 1 and old.completed_flag = 0 THEN
    new.complete_time := current_timestamp;
  END IF;
  RETURN new;
END;
$$;

CREATE TRIGGER update_timestamp
BEFORE UPDATE OF completed_flag ON tbl_pXXXX
FOR EACH ROW 
EXECUTE PROCEDURE completed_timestamp();

answered Jul 1, 2015 at 0:53

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql - trigger, update timestamp on field update

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related