* CREATE OR REPLACE FUNCTION timelog() RETURNS trigger AS $BODY$ DECLARE t_ix real; n int;
BEGIN IF NEW.time_type = 'Start' THEN SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.stmtserial 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;
ELSE IF NEW.time_type = 'Lap' THEN SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND t.time_type IN ('Start', 'Lap') ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix; GET DIAGNOSTICS n = ROW_COUNT; IF (n = 0) THEN t_ix := 1; ELSE t_ix := t_ix + 0.1; END IF; END IF; END IF; NEW.time_index = t_ix; return NEW; END $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION timelog() OWNER TO postgres;*
I only looked at the trigger function but the degree of duplication hurt so I decided to suggest an untested alternative to consider.
--Not Tested
SELECT
COALESCE(
--window function gives you the total count while still returning one row.
--put your logic inside the query
(SELECT CASE WHEN count(*) OVER () = 1
THEN t.time_index
ELSE t.time_index + 0.1
END
FROM table_ebscb_spa_log04 t
WHERE t.fn_name = NEW.fn_name AND
(
t.time_type = 'Start' --you always want start time
OR
t.time_type = NEW.time_type --and also (logical or) Lap time if that matches the NEW value
)
ORDER BY t.stmtserial DESC
LIMIT 1
),
--scalar sub-query returns NULL if not matching records found; coalesce then checks the next argument and, in this case, returns the non-null value of 1
