postgresql - 在基于其他列的列中插入一个实数 OLD INSERTs

Question

在 PostgreSQL 中，我有这个表...(在最左侧的“stmtserial”中有一个主键序列列，此图中未显示)

在上表中，所有列都是通过查询输入的，除了“time_index”是通过 BEFORE INSERT，PER-ROW 触发器自动填充的。

这是创建相同表(没有任何值)的代码，因此每个人都可以使用 Postgre SQL 查询面板创建它。

    CREATE TABLE table_ebscb_spa_log04
(
  pcnum smallint,
  stmtserial integer NOT NULL DEFAULT nextval('table_ebscb_spa_log04_stmtnum_seq'::regclass),
  fn_name character varying,
  "time" timestamp without time zone,
  time_elapse character varying,
  time_type character varying,
  time_index real,
  CONSTRAINT table_ebscb_spa_log04_pkey PRIMARY KEY (stmtserial)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE table_ebscb_spa_log04
  OWNER TO postgres;

我几乎完成了触发器的前 n 个第二部分，但我想知道在进入第三部分之前我是否采用了正确的方法。

触发器的第一部分生成表格图像中每个红色突出显示的方 block ，换句话说，它这样做...

根据每行中 fn_name 和 time_type 列的插入值在 time_index 列中插入一个数字。

如果 (fn_name 和 time_type) 进行的组合(例如，检查邮件 - 开始)在之前(以上)的任何行中都不存在，则 INSERT 1 在 time_index 列中。

Elif both (fn_name and time_type) 做一个确实存在于之前某行(上)的组合，然后插入前一个(上)中的数字后面的数字) 匹配，在 time_index 列中。

触发器的第二部分生成表格图像中每个绿色突出显示的方 block ，换句话说，它这样做...

根据每行中“fn_name”和“time_type”列的插入值，在“time_index”列中插入一个数字。

如果在 time_type 列中插入了“Lap”，则自动填充同一行的 time_index，其编号与前一个(上方)time_index 单元格 WHERE time_type = 'Start' 和 fn_name = 中的编号相同插入“Lap”的行；后跟一个点；然后是前一个 time_index 单元格中小数点后的数字 WHERE time_type 和 fn_name = 插入“Lap”的行中的数字，它们不在任何行之前(上方)WHERE time_type = 'Start' 和 fn_name = 到插入“Lap”的那一行。如果没有人，则从 1 开始计数(因此将为 0.1)。

所以，这就是我到目前为止所做的......(请注意我在 ELSE IF 之后犯的错误)

CREATE OR REPLACE FUNCTION timelog()
  RETURNS trigger AS
$BODY$
DECLARE
t_ix real;
n int;

BEGIN
IF NEW.time_type = 'Start' THEN
    SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix;
      GET DIAGNOSTICS n = ROW_COUNT;
        IF (n = 0) THEN 
        t_ix := 1; --this is ok, here it's really necessary to set it to 1
        ELSE
        t_ix := t_ix + 1;
        END IF;

ELSE 
    IF NEW.time_type = 'Lap' THEN 
        SELECT t.time_index  FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND (t.time_type = 'Start' OR time_type = 'Lap') ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix;
          GET DIAGNOSTICS n = ROW_COUNT;
            IF (n = 0) THEN 
            t_ix := 1; --!!!HERE I MADE A MISTAKE (SORRY) IT SHOULDN'T BE SET TO 1, IT SHOULD THROW AN ERROR MESSAGE 'There isn't any previous same fn_name with time_type Start';
            ELSE 
            t_ix := t_ix + 0.1;
            END IF;
  END IF;
END IF;
NEW.time_index = t_ix;
return NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION timelog()
  OWNER TO postgres;

这是创建触发器的代码:

CREATE TRIGGER timelog
    BEFORE INSERT ON table_ebscb_spa_log04
    FOR EACH ROW
    EXECUTE PROCEDURE timelog();

现在，触发器的第三部分在表格图像中生成蓝色突出显示的方 block ，但也影响绿色突出显示的方 block 的操作方式，换句话说，它这样做...

根据每一行中“fn_name”和“time_type”列的插入值，在“time_index”列中插入一个数字。如果 Break 在 time_type 列中插入，则自动填充同一行的 time_index 单元格，其编号与前一个(上方)time_index 单元格相同，其中 time_type = Start 和 fn_name = 到“Break”所在行已插入；后跟一个点；然后是前一个 time_index 单元格中小数点后的数字 WHERE time_type 和 fn_name = 插入“Break”的行中的数字，它们不在任何单元格之前(上方) WHERE time_type = 'Start' 和 ' fn_name' = 插入“Break”所在行中的那个。如果没有人，则从 1 开始计数。

希望一些优秀的 PostgreSQL 程序员可以帮助我。我已经阅读了许多 postgres 文档章节，但毫 headless 绪。

感谢高级。

Answer 1

基于前两个需求，你的触发器本身没有问题，但是你可以大大简化它:

CREATE OR REPLACE FUNCTION timelog() RETURNS trigger AS $BODY$
DECLARE
  t_ix real;
BEGIN
  -- First check if you need to change NEW at all
  IF (NEW.time_type = 'Start') OR (NEW.time_type = 'Lap') THEN
    -- Now perform the expensive lookup for either of 'Start' or 'Lap'
    SELECT time_index INTO t_ix
    FROM table_ebscb_spa_log04
    WHERE fn_name = NEW.fn_name
      AND (time_type = 'Start' OR time_type = 'Lap')
    ORDER BY stmtserial DESC LIMIT 1;

    IF NOT FOUND THEN
      -- Nothing found, so NEW.time_index := 1
      NEW.time_index := 1; 
    ELSIF NEW.time_type = 'Start' THEN 
      -- Start new index for fn_name, discard any fractional part, then increment
      NEW.time_index := floor(t_ix) + 1; 
    ELSE
      -- Continue the lap, increment NEW.time_index
      NEW.time_index := t_ix + 0.1; 
    END IF;
  END IF;
  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql;

但是，有一种更简单的方法，它也可以毫无问题地满足第三个要求。与其查看“time_index”值，不如查看“time”值，因为这是“time_index”所基于的:

CREATE OR REPLACE FUNCTION timelog() RETURNS trigger AS $BODY$
DECLARE
  t_ix real;
BEGIN
  -- Find the most recent entry for the same "fn_name" as the new record
  SELECT time_index INTO t_ix
  FROM table_ebscb_spa_log04
  WHERE fn_name = NEW.fn_name
  ORDER BY time DESC LIMIT 1;

  -- Nothing found, so NEW.time_index := 1
  IF NOT FOUND THEN
    NEW.time_index := 1;
    RETURN NEW;
  END IF;

  -- Some record exists, so update "time_index" based on previous record
  CASE NEW.time_type 
    WHEN 'Start' THEN 
      -- Start new index for fn_name, discard any fractional part, then increment
      NEW.time_index := floor(t_ix) + 1; 
    WHEN 'Lap' THEN
      -- Continue the lap, increment NEW.time_index
      NEW.time_index := t_ix + 0.1; 
    ELSE
      -- Break, find previous break or start, increment by 0.1
      SELECT time_index + 0.1 INTO NEW.time_index
      FROM table_ebscb_spa_log04
      WHERE fn_name = NEW.fn_name
        AND (time_type = 'Start' OR time_type = 'Break')
      ORDER BY time DESC LIMIT 1;
  END CASE;

  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql;

这实现了您的逻辑，但请注意存在一些潜在的陷阱:

• 如果在“开始”之前插入“圈”或“休息”会怎样？
• 如果在“开始”之后有超过 9 个“fn_name”事件怎么办(“time_index”小数部分将滚动到下一个整数)？

如果您的数据模型允许(与“time_elapse”相同)，您当然可以完全忘记“time_index”字段和触发器，并在 View 中动态生成它。