在其他列的基础上将实数插入OLD INSERT [英] 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)
在上表中，所有列均通过查询输入，但 time_index除外，后者通过INSERT，PER-ROW触发器自动填充。
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 和<$ c的INSERTed值在time_index列中插入一个数字每行中的$ c> time_type 列。
如果两者（ 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.
触发器的第二部分产生s表格图像中每个绿色突出显示的正方形，换句话说，就是这样做的。
The second part of the trigger produces every green highlighted square in the table image, in other words it does this...
在 time_index列中基于INSERTed值插入一个数字每行中的 fn_name和 time_type列。
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单元格相同的数字，其中time_type ='开始'，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).
所以，这就是我到目前为止所做的...（请注意在其他情况下我犯的错误）
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_index列中插入一个数字每行中的和 time_type列。
如果在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:
- 如果在开始之前插入 Lap或 Break怎么办？
- 如果还有更多内容怎么办开始后出现9个以上 fn_name事件（ time_index小数部分将移至下一个整数）？
您如果您的数据模型允许的话，当然可以忘记 time_index字段和触发器，并在视图中即时生成它（与 time_elapse相同）。
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 INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
