sql - POSTGRESQL pl/pgsql 触发器函数在插入时返回 null

Question

我有一个这样的 pg 表:

CREATE TABLE order_status_history (
order_id integer NOT NULL,
status character varying NOT NULL,
sequence integer DEFAULT 1 NOT NULL,
date_status timestamp with time zone DEFAULT now() NOT NULL,
record_state character varying(12) DEFAULT 'ACTIVE'::character varying NOT NULL
);

在 order_id 和序列上使用复合 PK。对于每个单独的 order_id，status 列本质上是一个从 1 开始的自动递增值。因此，如果 order_id 2 已经有两行，则序列将为 1 和 2，对于新行，序列应为 3。我试图在插入之前使用触发器来实现此行为，但是当我尝试为新的 order_id 插入第一行时(即触发器不必在插入之前更改行)，我收到错误对于PG。说它不能将 NULL 插入序列中。我看不出我的触发函数如何返回 NULL，但我的 pl/sql 不是很好，所以我确定它很简单...下面的触发函数，谢谢。

DECLARE
    seq_no INTEGER;

BEGIN
    SELECT INTO seq_no MAX(sequence) FROM rar.order_status_history WHERE order_id = NEW.order_id;
    IF FOUND THEN 
        NEW.sequence := seq_no + 1;
    END IF;

    RETURN NEW;
END;

Answer 1

如果该 order_id 的 order_status_history 中没有任何内容，则 MAX(sequence) 将为空。使用 COALESCE(MAX(sequence),0) 使其默认为 1。

你可以简单地写:

NEW.sequence := (SELECT COALESCE(MAX(sequence),0) FROM /* etc.. */) + 1;

在执行此操作之前，您确实应该以独占模式锁定订单行，以允许它处理同时插入同一订单历史记录的多个事务。即:

SELECT 1 FROM orders WHERE orders.order_id = NEW.order_id FOR UPDATE;