From: ohmy9od@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Re: Issue with plpgsql trigger
Date: 22 Jul 2004 15:45:52 -0700
Message-ID: <ffde43bc.0407221445.61b5a876@posting.google.com> (raw)
References: <ffde43bc.0407220217.2aad1715@posting.google.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ohmy9od@yahoo.com
Subject: Re: Issue with plpgsql trigger
In-Reply-To: <ffde43bc.0407221445.61b5a876@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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