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

问题描述

在 PostgreSQL 中，我有这张表...(最左侧的timestamp02"中有一个主键，该图未显示，请不要打扰，这对本问题的目的并不重要)

在上表中，所有列都是通过查询输入的，除了time_index"，我希望在每次填充每一行时通过触发器自动填充它.

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

CREATE TABLE table_ebscb_spa_log02(pcnum smallint，timestamp02 时间戳与时区 NOT NULL DEFAULT now(),fn_name 字符不同，没有时区的时间"时间，time_elapse 字符变化，time_type 字符变化，time_index 实数，CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02))和 (OIDS=FALSE);ALTER TABLE table_ebscb_spa_log02postgres 的所有者；

我希望触发器做的是:

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

如果 ("fn_name" 和 "time_type") 进行组合(例如，检查邮件 - 开始)，并且之前(上方)的任何行都不存在，则在time_index"列中插入 1，

Elif 两者(fn_name"和time_type")在之前(上方)的某行中进行组合，然后在time_index"列中插入之前(上方)后面的数字.

(请看示例表格图像，此触发器将在其上生成每个红色突出显示的方块)

我看过很多 PostgreSQL 教程视频，阅读了很多手册，包括这些

http://www.postgresql.org/docs/9.4/static/sql-createtrigger.htmlhttp://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html

没有任何结果.

到目前为止我已经尝试过这个来创建函数:

CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$宣布t_ix 实数；n 整数；开始IF NEW.time_type = 'Start' THENSELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1 INTO t_ix;获取诊断信息 n = ROW_COUNT；如果 (n = 0) 那么t_ix = 1;别的t_ix = t_ix + 1;万一;万一;NEW.time_index = t_ix;返回新的；结尾$$语言 plpgsql;

这是创建查询:

CREATE TRIGGER on_ai_myTable插入 table_ebscb_spa_log02 后每行执行程序 on_ai_myTable();

然后当我在表中手动插入值时，没有任何变化(没有错误消息)time_index 列只是保持为空，我做错了什么???

请一些好的PostgreSQL程序员帮我一把，我在这个任务中真的到了死点，我有更多的想法.

提前致谢

解决方案

在 AFTER INSERT 触发器中，您对 NEW.time_index 所做的任何更改都将被忽略.此时记录已经插入；已经来不及修改了.

改为将触发器创建为 BEFORE INSERT.

In PostgreSQL I have this table... (there is a primary key in the most left side "timestamp02" which is not shown in this image, please don't bother, its not important for the purpose of this question)

in the table above, all columns are entered via queries, except the "time_index" which I would like to be filled automatically via a trigger each time each row is filled.

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_log02
(
  pcnum smallint,
  timestamp02 timestamp with time zone NOT NULL DEFAULT now(),
  fn_name character varying,
  "time" time without time zone,
  time_elapse character varying,
  time_type character varying,
  time_index real,
  CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE table_ebscb_spa_log02
  OWNER TO postgres;

What I would like the trigger to do is:

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 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 both ("fn_name" and "time_type") do a combination that does exist in some row before (above), then INSERT the number following the one before(above) in the "time_index" column.

(pls look at the example table image, this trigger will produce every red highlighted square on it)

I have watch many, PostgreSQL tutorial videos, read many manuals, including these

http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html http://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html

without any result.

I have tried so far this to create the function:

CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$
DECLARE
t_ix real;
n int;

BEGIN
IF NEW.time_type = 'Start' THEN
    SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1 INTO t_ix;
      GET DIAGNOSTICS n = ROW_COUNT;
        IF (n = 0) THEN 
        t_ix = 1;
        ELSE 
        t_ix = t_ix + 1;
        END IF;
END IF;
NEW.time_index = t_ix;
return NEW;
END
$$
LANGUAGE plpgsql;

And this to create the query:

CREATE TRIGGER on_ai_myTable
    AFTER INSERT ON table_ebscb_spa_log02
    FOR EACH ROW
    EXECUTE PROCEDURE on_ai_myTable();

Then when I manually insert the values in the table, nothing change (no error message) time_index column just remain empty, what am I doing wrong???

Please some good PostgreSQL fellow programmer could give me a hand, I really have come to a death point in this task, I have any more ideas.

Thanks in advance

解决方案

In an AFTER INSERT trigger, any changes you make to NEW.time_index will be ignored. The record is already inserted at this point; it's too late to modify it.

Create the trigger as BEFORE INSERT instead.

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