Search Postgresql Archives

trigger question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux