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

in postgresql have table... (there primary key in left side "timestamp02" not shown in image, please don't bother, not important purpose of question)

in table above, columns entered via queries, except "time_index" filled automatically via trigger each time each row filled.

this code create same table (without value) create using postgre sql query panel.

create table table_ebscb_spa_log02 (   pcnum smallint,   timestamp02 timestamp 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) ) (   oids=false ); alter table table_ebscb_spa_log02   owner postgres; 

what trigger is:

insert number in "time_index" column based on inserted values of "fn_name" , "time_type" columns in each row.

if both ("fn_name" , "time_type") combination (eg. check mails - start) doesn't exist in row before (above), insert 1 in "time_index" column,

elif both ("fn_name" , "time_type") combination exist in row before (above), insert number following 1 before(above) in "time_index" column.

(pls @ example table image, trigger 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 result.

i have tried far create function:

create or replace function on_ai_mytable() returns trigger $$ declare t_ix real; n int;  begin if new.time_type = 'start'     select t.time_index table_ebscb_spa_log02 t t.fn_name = new.fn_name , t.time_type = 'start' order t.timestamp02 desc limit 1 t_ix;       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; return new; end $$ language plpgsql; 

and create query:

create trigger on_ai_mytable     after insert on table_ebscb_spa_log02     each row     execute procedure on_ai_mytable(); 

then when manually insert values in table, nothing change (no error message) time_index column remain empty, doing wrong???

please postgresql fellow programmer give me hand, have come death point in task, have more ideas.

thanks in advance

in after insert trigger, changes make new.time_index ignored. record inserted @ point; it's late modify it.

create trigger before insert instead.