在基于其他列的列中插入实数 OLD INSERTs [英] INSERT a real number in a column based on other columns OLD INSERTs

问题描述

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

In PostgreSQL I have this table... (there is a primary key serial column in the most left side "stmtserial" which is not shown in this image)

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

in the table above, all columns are entered via queries, except the "time_index" which is automatically filled via a BEFORE INSERT, PER-ROW trigger.

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

This is the code to create the same table (without any value) so everyone could create it using the Postgre SQL query panel.

    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 个第二部分，但我想知道在进入第三部分之前我是否采取了正确的方法.

I've almost done the first n second part of the trigger, but I'm wondering if I'm taking the right approach before getting to the third part.

触发器的第一部分在表格图像中生成每个红色突出显示的方块，换句话说，它执行此操作...

The first part of the trigger produces every red highlighted square in the table image, in other words it does this...

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

INSERT a number in the time_index column based on the INSERTed values of the fn_name and time_type columns in each row.

如果 (fn_name 和 time_type) 做了一个组合(例如检查邮件 - 开始)，之前(上面)的任何行都不存在，然后插入time_index 列中的 1.

If both (fn_name and time_type) do a combination (eg. Check Mails - Start) that doesn't exist in any row before (above), then INSERT 1 in the time_index column.

Elif (fn_name 和 time_type) 都做了一个组合，该组合确实存在于前一行(上)，然后插入前一行(上)之后的数字) 匹配，在 time_index 列中.

Elif both (fn_name and time_type) do a combination that does exist in some row before (above), then INSERT the number following the one in the previous(above) match, in the time_index column.

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

The second part of the trigger produces every green highlighted square in the table image, in other words it does this...

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

INSERT a number in the "time_index" column based on the INSERTed values of the "fn_name" and "time_type" columns in each row.

如果在 time_type 列中插入了 'Lap'，则自动填充同一行的 time_index，与前一个(以上)time_index 单元格的编号相同 WHERE time_type = 'Start' and fn_name = 到插入Lap"的行；后跟一个点；然后是前一个 time_index 单元格中小数点后面的数字 WHERE time_type 和 fn_name = 到插入 'Lap' 的行中的那些，不在(上方)任何行 WHERE time_type = 'Start' 和 fn_name 之前= 插入Lap"的那一行.如果没有人，则从 1 开始数(所以会是 0.1).

If 'Lap' is INSERTed in the time_type column, then automatically fill the time_index of the same row, with the same number as the previous(above) time_index cell WHERE time_type = 'Start' and fn_name = to the one in the row where 'Lap' was INSERTed; followed by a dot; and followed by the number that follows the decimal one in the previous time_index cell WHERE time_type and fn_name = to the ones in the row where 'Lap' was INSERTed, that are not before(above) any row WHERE time_type = 'Start' and fn_name = to the one in the row where 'Lap' was INSERTed. If there isn't anyone, then start counting from 1 (so it would be 0.1).

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

So, this is what I have made so far... (PLEASE NOTE THE MISTAKE I DID AFTER THE 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;

这是创建触发器的代码:

This is the code to create the trigger:

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

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

Now, the third part of the trigger produces the blue highlighted square in the table image, but also affects the way the green highlighted squere operate, in other words it does this...

根据每行中fn_name"和time_type"列的 INSERTed 值在time_index"列中插入一个数字.如果在 time_type 列中插入 Break，则自动填充同一行的 time_index 单元格，使用与前一个(以上)time_index 单元格相同的编号 WHERE time_type = Start 和 fn_name = 到 'Break' 所在行中的那个插入;后跟一个点；然后是前一个 time_index 单元格 WHERE time_type 和 fn_name = 到插入 'Break' 的行中的小数点后面的数字，即不在(上方)任何单元格 WHERE time_type = 'Start' 和 ' 之前的数字fn_name' = 插入 'Break' 的行中的那个.如果没有人，则从 1 开始数.

INSERT a number in the "time_index" column based on the INSERTed values of the "fn_name" and "time_type" columns in each row. If Break is INSERTed in the time_type column, then automatically fill the time_index cell of the same row, with the same number as the previous(above) time_index cell WHERE time_type = Start and fn_name = to the one in the row where 'Break' was INSERTed; followed by a dot; and followed by the number that follows the decimal one in the previous time_index cell WHERE time_type and fn_name = to the ones in the row where 'Break' was INSERTed, that are not before(above) any cell WHERE time_type = 'Start' and 'fn_name' = to the one in the row where 'Break' was INSERTed. If there isn't anyone, then start counting from 1.

希望一些优秀的 PostgreSQL 程序员伙伴可以帮助我.我已经阅读了许多 postgres 文档章节，但一无所知.

Hope some good PostgreSQL fellow programmer could give me a hand. I have read many of postgres documentation chapters, with no clue.

感谢高级.

推荐答案

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

Based on the first two requirements, there is nothing wrong with your trigger per se, but you can greatly simplify it:

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"的基础:

There is a much easier way, however, and that will also accommodate the third requirement without a problem. Rather than looking at the "time_index" values, you should look at the "time" value, because that is what "time_index" is based upon:

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;

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

This implements your logic but do note that there are some potential pitfalls:

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

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

You could of course forget about the "time_index" field and the trigger altogether and generate it on the fly in a view, if your data model allows it (same with "time_elapse").

这篇关于在基于其他列的列中插入实数 OLD INSERTs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！