[SQL] Issue with plpgsql trigger

[Oeln]

I'm only learning this, but I've got a working function that I want to
be invoked in a trigger. The function itself is:


CREATE FUNCTION online_pct_func(integer, interval)
RETURNS boolean AS '

DECLARE

        on numeric;
        off numeric;

        o_pct numeric;

        op varchar;

BEGIN

        on := 0;
        off := 0;

        SELECT count(info.online) INTO on
        FROM info
        WHERE info.id = $1
        AND info.online = ''1''
        AND info.iso_date_time > (now() - CAST($2 AS interval));

        SELECT count(info.online) INTO off
        FROM info
        WHERE info.id = $1
        AND info.online = ''0''
        AND info.iso_date_time > (now() - CAST($2 AS interval));

        o_pct := (on / (on + off)) * 100.0;

        op := ''UPDATE online_pct '';

        IF $2 = ''01:00'' 
        THEN op := op || ''SET on1hr = '' 
        || o_pct || '', isodt1hr = current_timestamp '';
        ELSIF $2 = ''1 day'' 
        THEN op := op || ''SET on1day = '' 
        || o_pct || '', isodt1day = current_timestamp '';
        ELSIF $2 = ''1 week'' 
        THEN op := op || ''SET on1wk = '' 
        || o_pct || '', isodt1wk = current_timestamp '';
        ELSIF $2 = ''1 mon'' 
        THEN op := op || ''SET on1mo = '' 
        || o_pct || '', isodt1mo = current_timestamp '';
        ELSIF $2 = ''1 year'' 
        THEN op := op || ''SET on1yr = '' 
        || o_pct || '', isodt1yr = current_timestamp '';
        END IF;

        op := op || ''WHERE id = '' || $1;

        EXECUTE op;

        RETURN 1;

END;

' LANGUAGE 'plpgsql';


It gives the intended outcome by itself, for example with select
online_pct_func(1, '1 year');

The trigger I've got, which is not working for me, is as follows: 


CREATE FUNCTION online_pct_trig() RETURNS opaque AS '

DECLARE

        i1hr    timestamp;
        i1day   timestamp;
        i1wk    timestamp;
        i1mo    timestamp;
        i1yr    timestamp;

        oper    varchar;

BEGIN

        SELECT  o.isodt1hr INTO i1hr,
                o.isodt1day INTO i1day,
                o.isodt1wk INTO i1wk,
                o.isodt1mo INTO i1mo,
                o.isodt1yr INTO i1yr
        FROM online_pct o
        WHERE o.id = NEW.id;

        oper := ''SELECT online_pct_func(NEW.id, '';

        IF i1hr <= (now() - ''00:01''::interval) 
        THEN oper := oper || ''01:00'' || '');'';
        EXECUTE oper;
        END IF;
        IF i1day < (now() - ''01:00''::interval)
        THEN oper := oper || ''1 day'' || '');'';
        EXECUTE oper;
        END IF;
        IF i1wk < (now() = ''1 day''::interval)
        THEN oper := oper || ''1 week'' || '');'';
        EXECUTE oper;
        END IF;
        IF i1mo < (now() - ''1 week''::interval)
        THEN oper := oper || ''1 mon'' || '');'';
        EXECUTE oper;
        END IF;
        IF il1yr < (now() - ''1 mon''::interval)
        THEN oper := oper || ''1 year'' || '');'';
        EXECUTE oper;
        END IF;

        RETURN NEW;

END;

' LANGUAGE 'plpgsql';

CREATE TRIGGER online_pct_trig
AFTER INSERT ON info
FOR EACH ROW EXECUTE PROCEDURE online_pct_trig();


I'm guessing there's an obvious error in there that I can't identify;
but I've gotten nowhere with it on my own. I guess I'm looking for any
input on this..

- Oeln

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match