Re: INSERT a real number in a column based on other columns OLD INSERTs - Mailing list pgsql-general

From David G. Johnston
Subject Re: INSERT a real number in a column based on other columns OLD INSERTs
Date
Msg-id CAKFQuwb3sEbyXoGdhhpMv7mpAuvkgqkMYPFRyraCcEAS=tcGug@mail.gmail.com
Whole thread Raw
In response to Re: INSERT a real number in a column based on other columns OLD INSERTs  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
On Wed, Jun 24, 2015 at 5:52 PM, Adrian Klaver <adrian.klaver@aklaver.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.
 

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: INSERT a real number in a column based on other columns OLD INSERTs
Next
From: Andomar
Date:
Subject: Re: Include.d and warnings