From: | Apu Islam <apuislam(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | trigger question |
Date: | 2005-08-17 00:19:38 |
Message-ID: | d70f8db905081617195444c162@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a trigger which is not working properly.
The error I get is parse error at $1. I am putting the code here for
someone to see and comment on.
(p/s the double quotes are actually two single quotes)
best regards,
-apu
CREATE FUNCTION cust_call_update() RETURNS TRIGGER AS '
DECLARE
hour INT ;
zero_dur_count INT;
less_ten_dur_count INT ;
less_twenty_dur_count INT ;
greater_twenty_dur_count INT ;
total_calls_count INT ;
total_aggr_dur INT ;
prefix VARCHAR ;
tmp INT ;
BEGIN
SELECT INTO hour date_part(''hour'', NEW.h323connecttime) ;
SELECT INTO prefix substring( NEW.calledstationid from 1 for 4 ) ;
SELECT INTO tmp count(*) from customer_stat where prefix =
prefix and ip = NEW.cisconasport ;
IF tmp >= 1 THEN
SELECT INTO zero_dur_count, less_ten_dur_count,
less_twenty_dur_count,
greater_twenty_dur_count, total_calls_count,
total_aggr_dur zero_dur_count,
less_ten_dur_count, less_twenty_dur_count,
greater_twenty_dur_count,
total_calls_count, total_aggr_dur from customer_stat
where prefix =''prefix''
and IP = ''NEW.cisconasport'' ;
IF NEW.acctsessiontime = 0 THEN
UPDATE customer_stat SET zero_dur_count =
zero_dur_count + 1,
total_calls_count = total_calls_count + 1
where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
ELSE
IF NEW.acctsessiontime > 0 OR NEW.acctsessiontime[2] < 11 THEN
UPDATE customer_stat SET less_ten_dur_count =
less_ten_dur_count + 1,
total_calls_count = total_calls_count + 1,
total_aggr_dur = total_aggr_dur +
NEW.acctsessiontime where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
ELSE
IF NEW.acctsessiontime > 10 OR NEW.acctsessiontime < 21 THEN
UPDATE customer_stat SET less_twenty_dur_count
= less_twenty_dur_count+1,
total_calls_count = total_calls_count + 1,
total_aggr_dur = total_aggr_dur +
NEW.acctsessiontime where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
ELSE
UPDATE customer_stat SET
greater_twenty_dur_count = greater_twenty_dur_count+1,
total_calls_count = total_calls_count + 1,
total_aggr_dur = total_aggr_dur +
NEW.acctsessiontime where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
END IF ;
END IF ;
END IF ;
END IF ;
IF tmp = 0 THEN
INSERT INTO customer_stat VALUES (
''NEW.cisconasport'',NEW.h323connecttime,hour,0,0,0,0,0,''p$
SELECT INTO zero_dur_count, less_ten_dur_count,
less_twenty_dur_count,
greater_twenty_dur_count, total_calls_count,
total_aggr_dur zero_dur_count,
less_ten_dur_count, less_twenty_dur_count,
greater_twenty_dur_count,
total_calls_count, total_aggr_dur FROM customer_stat
where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
IF NEW.acctsessiontime = 0 THEN
UPDATE customer_stat SET zero_dur_count =
zero_dur_count + 1,
total_calls_count = total_calls_count + 1
where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
ELSE
IF NEW.acctsessiontime > 0 OR NEW.acctsessiontime < 11 THEN
UPDATE customer_stat SET less_ten_dur_count =
less_ten_dur_count + 1,
total_calls_count = total_calls_count + 1,
total_aggr_dur = total_aggr_dur +
NEW.acctsessiontime where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
ELSE
IF NEW.acctsessiontime > 10 OR NEW.acctsessiontime < 21 THEN
UPDATE customer_stat SET less_twenty_dur_count
= less_twenty_dur_count+1,
total_calls_count = total_calls_count + 1,
total_aggr_dur = total_aggr_dur +
NEW.acctsessiontime where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
ELSE
UPDATE customer_stat SET
greater_twenty_dur_count = greater_twenty_dur_count+1,
total_calls_count = total_calls_count + 1,
total_aggr_dur = total_aggr_dur +
NEW.acctsessiontime where prefix = ''prefix''
and IP = ''NEW.cisconasport'' ;
END IF ;
END IF ;
END IF ;
END IF;
RETURN NULL ;
END ;
' LANGUAGE 'plpgsql' ;
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-08-17 01:17:21 | Re: trigger question |
Previous Message | Kaare Rasmussen | 2005-08-16 21:17:56 | Re: Testing of MVCC |