Re: INSERT a real number in a column based on other columns OLD INSERTs

On Wed, Jun 24, 2015 at 5:52 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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;*
>>
>>
> Might want to reread this section:):
>
>
> 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.
​