postgresqltriggersplpgsqlpostgresql-triggers

How to acheive a "stolen_at" field which is populated when the "status" field is updated? TRIGGER function and GENERATED ALWAYS AS attempts attached

How to update stolen_at such that it is populated with the current timestamp when status is updated from INSTOCK to STOLEN?

I've attempted a trigger based approached but it contains a bug:

SQL Error [2F005]: ERROR: control reached end of trigger procedure without RETURN
  Where: PL/pgSQL function stolen()

The trigger approach:

CREATE TABLE items ( 
    id INTEGER PRIMARY KEY, 
    item_name text NOT NULL, 
    item_status text NOT NULL, 
    stolen_at TIMESTAMP WITHOUT TIME zone 
);


CREATE OR REPLACE FUNCTION stolen() RETURNS TRIGGER AS $CODE$ 
BEGIN
   -- Populate stolen_at with datetime only if item_status 
   -- changes from INSTOCK to STOLEN
   IF NEW.item_status = 'STOLEN' 
   AND OLD.item_status = 'INSTOCK' 
   THEN
      NEW.stolen_at : = now() AT TIME zone 'utc';
   END IF;
END $CODE$ LANGUAGE plpgsql;


CREATE TRIGGER populate_stoken 
AFTER INSERT OR UPDATE OF item_status 
ON items FOR EACH ROW EXECUTE PROCEDURE stolen();

The other approach explored was a function which takes in the id and new_item_status and populates the item_status field. I could not get it to work though:

CREATE TABLE items ( 
    id INTEGER PRIMARY KEY, 
    item_name text NOT NULL, 
    item_status text NOT NULL, 
    stolen_at TIMESTAMP WITHOUT TIME zone NOT NULL GENERATED ALWAYS AS (stolen2(id, item_status)) STORED 
);

CREATE OR REPLACE FUNCTION stolen2(id INT, new_item_status text) RETURNS TIMESTAMP AS $CODE$ 
DECLARE stolen_at TIMESTAMP WITHOUT TIME zone;
BEGIN
   SELECT
      * 
   FROM
      items 
   WHERE
      id = id 
      AND item_status = 'INSTOCK';      
      
   -- Return if conditions satisfy otherwise return null 
   IF found AND new_item_status = 'STOLEN' 
   THEN
      RETURN now() AT TIME zone 'utc';
   ELSE
      RETURN NULL;
END IF;
END $CODE$ LANGUAGE plpgsql;

Which of the two approaches is preferred (e.g. less resource intensive) and what is the correct way to implement them?

Solution

  • Your first trigger is almost fine, but two things are wrong: