时间戳

首页 > 解决方案 > 如何实现“状态”字段更新时填充的“被盗”字段？TRIGGER 函数和 GENERATED ALWAYS AS 尝试附加

postgresql - 如何实现“状态”字段更新时填充的“被盗”字段？TRIGGER 函数和 GENERATED ALWAYS AS 尝试附加

问题描述

如何更新，以便在从to更新stolen_at时填充当前时间戳？statusINSTOCKSTOLEN

我尝试了一个基于触发器的方法，但它包含一个错误：

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

触发方法：

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();

探索的另一种方法是接收idandnew_item_status并填充该item_status字段的函数。我无法让它工作：

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;

这两种方法中的哪一种是首选（例如资源密集度较低），实现它们的正确方法是什么？

标签： postgresqltriggersplpgsqlpostgresql-triggers

解决方案

您的第一个触发器几乎没问题，但有两件事是错误的：

  • 它必须是一个BEFORE触发器，以便您可以修改新行

  • RETURN NEW;你在决赛前就失踪了END;（见这里进行讨论）

推荐阅读