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
(
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 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;
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.
--
View this message in context:
http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
