Might want to reread this section:):On 06/23/2015 11:20 PM, litu16 wrote:
So, this is what I have made so far...
* 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;*
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS
40.6.2.2. IF-THEN-ELSE
Examples:
IF parentid IS NULL OR parentid = ''
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
1) --default of 1 if no matching records found
INTO t_ix;
NEW.time_index = t_ix;
David J.
