Search Postgresql Archives

Re: trigger question

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

 



[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

---------------------------(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