Skip site navigation (1) Skip section navigation (2)

Re: Issue with plpgsql trigger

From: ohmy9od(at)yahoo(dot)com (Oeln)
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Issue with plpgsql trigger
Date: 2004-07-22 22:45:52
Message-ID: ffde43bc.0407221445.61b5a876@posting.google.com (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-sql by date

Next:From: Chris CoxDate: 2004-07-22 23:46:27
Subject: Re: LIKE on index not working
Previous:From: Tom LaneDate: 2004-07-22 20:34:05
Subject: Re: Converting a plperlu function to a plpgsql function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group