postgresql - INSERT a number in a column based on other columns OLD INSERTs

Question

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

Answer 1

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.