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

问题描述

在PostgreSQL中，我有此表...（最左侧的 timestamp02中有一个主键，该键未在此图中显示，请不要打扰，对于这个问题，它并不重要）

在上表中，所有列都是通过查询输入的，但 time_index除外，我希望每次填充行时都会通过触发器自动填充 time_index。

这是用于创建同一表（无任何值）的代码，因此每个人都可以使用Postgre SQL查询面板创建该表。

 创建表table_ebscb_spa_log02 
（
 pcnum smallint，
 timestamp02时区的时间戳记非空默认now（），
 fn_name字符变化，
时间时间，不带时区，
 time_elapse字符变化，
 time_type字符变化，
 time_index实数，
 CONSTRAINT table_ebscb_sp a_log02_pkey主键（timestamp02）
）
 WITH（
 OIDS = FALSE 
）; 
 ALTER TABLE table_ebscb_spa_log02 
拥有者到postgres； 
  

我希望触发操作是：

根据每行 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.html
http：// www .postgresql.org / docs / 9.4 / static / plpgsql-trigger.html

没有任何结果。

到目前为止，我已经尝试过创建函数：

 创建或替换功能on_ai_myTable（）返回触发信息为$$ 
声明
 t_ix real; 
 n int; 
 
 BEGIN 
如果NEW.time_type ='Start'然后
从table_ebscb_spa_log02选择t.time_index t.fn_name = NEW.fn_name AND t.time_type ='Start'ORDER通过t.timestamp02 DESC LIMIT 1 INTO t_ix; 
 GET DIAGNOSTICS n = ROW_COUNT; 
 IF（n = 0）然后
 t_ix = 1; 
 ELSE 
 t_ix = t_ix + 1; 
 END IF; 
 END IF; 
 NEW.time_index = t_ix; 
返回NEW； 
 END 
 $$ 
语言plpgsql; 
  

然后创建查询：

 创建触发器on_ai_myTable 
后在table_ebscb_spa_log02上插入
每行
执行步骤on_ai_myTable（）; 
  

然后，当我在表中手动插入值时，没有任何变化（无错误消息），time_index列仍然是空的，我在做什么错？？

请一些优秀的PostgreSQL程序员来帮帮我，我真的已经死了，我还有其他想法。

预先感谢

解决方案

在之后插入触发器，您对 NEW.time_index 所做的任何更改都将被忽略。记录已插入到这一点；

相反，将触发器创建为，然后再插入。

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 INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！