pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Issue with plpgsql trigger
2+ messages / 1 participants
[nested] [flat]

* Issue with plpgsql trigger
@ 2004-07-22 10:17 Oeln <ohmy9od@yahoo.com>
  2004-07-22 22:45 ` Re: Issue with plpgsql trigger Oeln <ohmy9od@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: ohmy9od@yahoo.com @ 2004-07-22 10:17 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Issue with plpgsql trigger
  2004-07-22 10:17 Issue with plpgsql trigger Oeln <ohmy9od@yahoo.com>
@ 2004-07-22 22:45 ` Oeln <ohmy9od@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: ohmy9od@yahoo.com @ 2004-07-22 22:45 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2004-07-22 22:45 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-22 10:17 Issue with plpgsql trigger Oeln <ohmy9od@yahoo.com>
2004-07-22 22:45 ` Oeln <ohmy9od@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox