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?
