trigger question

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' ;

Responses

Browse pgsql-general by date

  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