Postgresql:在插入函数之前更新

Question

我在为触发器创建函数时遇到问题。我想在将数据插入数据库之前更新插入的值。我的代码如下所示:

CREATE OR REPLACE FUNCTION test_func()
RETURNS TRIGGER AS 
$$
DECLARE cnt INTEGER;
BEGIN
    cnt := COUNT(*) FROM sample_tbl WHERE id = NEW.id AND created_date = NEW.created_date;
    NEW.current_order := cnt + 1; // I want to set value of sample_tbl.current_order automatically
END 
$$ LANGUAGE plpgsql;


CREATE TRIGGER test_trigger
BEFORE INSERT
ON test_tbl
FOR EACH ROW
EXECUTE PROCEDURE test_func();

我插入数据然后 IDE 说:控件到达触发过程的结尾而没有返回
其中:PL/pgSQL 函数 test_func()

Answer 1

错误表明您必须从触发器返回一些东西(NEW 或 NULL)

这不需要触发器。使用此选择查询的简单 View 将为您提供所需的结果

--create or replace view sample_view as
select t.id, t.created_date,
       row_number() OVER ( partition by  id,created_date order by id ) as current_order
FROM sample_tbl t;

如果使用触发器更新，这将与记录完全匹配

CREATE OR REPLACE FUNCTION test_func()
RETURNS TRIGGER AS 
$$
DECLARE cnt INTEGER;
BEGIN
    select COUNT(*) INTO cnt FROM sample_tbl WHERE id = NEW.id 
      AND created_date = NEW.created_date;
    NEW.current_order := cnt + 1; 
RETURN NEW; --required
END 
$$ LANGUAGE plpgsql;

Demo