Обсуждение: trigger question

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

On Tue, Aug 16, 2005 at 07:19:38PM -0500, Apu Islam wrote:
> 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.

When do you get the error?  When you create the function, or when
the trigger calls it?  What version of PostgreSQL are you using?

>                 INSERT INTO customer_stat VALUES (
> ''NEW.cisconasport'',NEW.h323connecttime,hour,0,0,0,0,0,''p$

The above INSERT command looks odd.  Why do you quote NEW.cisconasport?
Where does the last pair of single quotes having a closing pair?
Where are the closing parenthesis and statement-terminating semicolon?

--
Michael Fuhr

[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]

On Wed, Aug 17, 2005 at 08:17:43PM -0500, Apu Islam wrote:
> However, I still get the error.. here is a sample very trim down version.
> I think the "hour" is the problem child. Anyone can give me some clues
> how to manage the variable substitution and string quoting on this
> trigger.
>
> CREATE FUNCTION cust_call_update() RETURNS TRIGGER AS '
> DECLARE
>         hour                            INT ;
> BEGIN
>         SELECT INTO hour date_part(''hour'', NEW.h323connecttime) ;
>
>         INSERT INTO customer_stat (ip,connecttime,hour) VALUES
> (NEW.cisconasport, NEW.h323connecttime, date_part("hour",
> NEW.h323connecttime)) ;
>
> RETURN NULL ;
> END ;
> 'LANGUAGE 'plpgsql' ;

You're using a variable name (hour) that's the same as a column
name.  When PL/pgSQL prepares the INSERT statement it thinks you
want the variable's value where you have the column name, so you
get an error.  Rename the variable hour to something else.

Also, in this example, the first argument to date_part() should be
in single quotes (doubled or escaped since you're already inside a
quoted string), not double quotes.

--
Michael Fuhr