Unanswered: INSERT a number in a column based on other columns OLD INSERTs
In PostgreSQL I have this table... (there is a primary key in the most left side "timestamp02" which is not shown in this image)
in the table above, all columns are entered via querrys, 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
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,
CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02)
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 tried so far this to create the function:
CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$
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;
t_ix = t_ix + 1;
NEW.time_index = t_ix;
But 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.