Issue with plpgsql trigger

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

Re: Issue with plpgsql trigger

Only wanted to indicate further that I know that IF loop is bad logic
in itself, where oper will get overwritten in each case. I've now got
the following instead:

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;

IF i1hr <= (now() - ''00:01''::interval)
THEN oper := ''SELECT online_pct_func(NEW.id, '';
oper := oper || ''01:00'' || '');'';
EXECUTE oper;
END IF;
IF i1day < (now() - ''01:00''::interval)
THEN oper := ''SELECT online_pct_func(NEW.id, '';
oper := oper || ''1 day'' || '');'';
EXECUTE oper;
END IF;
IF i1wk < (now() = ''1 day''::interval)
THEN oper := ''SELECT online_pct_func(NEW.id, '';
oper := oper || ''1 week'' || '');'';
EXECUTE oper;
END IF;
IF i1mo < (now() - ''1 week''::interval)
THEN oper := ''SELECT online_pct_func(NEW.id, '';
oper := oper || ''1 mon'' || '');'';
EXECUTE oper;
END IF;
IF i1yr < (now() - ''1 mon''::interval)
THEN oper := ''SELECT online_pct_func(NEW.id, '';
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 get no errors when I create it; but it isn't invoking the function
either - that's what the issue is, but I'm not certain why or what
I've got to correct in order to get it to work. Thank for any input..

- Oeln

Re: Issue with plpgsql trigger

Okay well I've figured out the issue with this. I declare i1hr as
online_pct.isodt1hr%TYPE; instead of timestamp, etc. and include the
following for 'oper':

oper := ''SELECT online_pct_func('' || NEW.id;
oper := oper || '', ''''01:00'''');'';

I'd been going back and forth with escaping ' incorrectly,
essentially. I got no input on this but I figured it'd be impolite to
leave it open-ended now that I've fixed the issue..

- Oeln