Re: Error in creating the backend query

Below is the full stored procedure.
The error as captured in pg_log is as follows:

2009-07-29 11:19:01 MDT 172.20.1.33ERROR:  syntax error at or near "is"
at character 192
2009-07-29 11:19:01 MDT 172.20.1.33STATEMENT:  update tblksalerts set
cleartime = '2009-07-29 10:49:50'::TIMESTAMP, laststatusid = 7::INTEGER,
lastreplytext = '0'::VARCHAR, lasttesttime = '2009-07-29
10:49:50'::TIMESTAMP, lasteventsource is NULL::VARCHAR, lasteventid is
NULL::INTEGER, replyval = 0::REAL, trend = ''::varchar ( 1 ),
alertoccurrence = 0::integer where ksalertssysid = 1737060

The piece of code executing is:

                update tblksalerts
                  set cleartime =  x_cleartime,
                      laststatusid = x_statusid,
                      lastreplytext = x_replytxt,
                      lasttesttime =  x_testtime,
                      lasteventsource = x_eventsource,
                      lasteventid = myeventid,
                      replyval = x_replyval,
                      trend = mytrend,
                      alertoccurrence = testcount
                where ksalertssysid = alertsrecord.ksalertssysid;

and as you can see, every column is being cast.

------------------------------------------------------------------------
----------

-- Function: fn_dtaalerts(character varying, timestamp without time
zone, character varying, integer, character varying, integer, real,
character varying, integer, timestamp without time zone, timestamp
without time zone, integer, character varying, character varying,
integer)

-- DROP FUNCTION fn_dtaalerts(character varying, timestamp without time
zone, character varying, integer, character varying, integer, real,
character varying, integer, timestamp without time zone, timestamp
without time zone, integer, character varying, character varying,
integer);

CREATE OR REPLACE FUNCTION fn_dtaalerts(x_testhash character varying,
x_testtime timestamp without time zone, x_replytxt character varying,
x_statusid integer, x_eventsource character varying, x_eventid integer,
x_replyval real, x_eventlog character varying, x_counter integer,
x_cleartime timestamp without time zone, x_lasttesttime timestamp
without time zone, x_laststatusid integer, x_lastreplytxt character
varying, x_lasteventsource character varying, x_lasteventid integer)
  RETURNS void AS
$BODY$
/* function to insert or update alerts from dta*/
declare
    alertsrecord         record;
    r_testrecord         record;
    myresellerid         integer;
    mytrend              varchar(1);
    testcount            integer;
    oldstatusid          integer;
    mydisplayname        varchar(50);
    myeventid            integer;
    myeventsource       varchar(100);

begin
    select into r_testrecord
           laststatus,
           accountno,
           priority,
           ksdevicessysid,
           kstestssysid
    from tblkstests
    where testhash=x_testhash;

    select into mydisplayname displayname from tblksdevices where
ksdevicessysid=r_testrecord.ksdevicessysid;


    if x_eventsource is null then
       myeventsource = '';
    end if;

    if x_eventid is null then
       myeventid = 0;
    end if;

    if not exists (select 1 from tblkseventexclusion where
eventid=myeventid and eventsource=myeventsource
        and accountno = r_testrecord.accountno and
displayname=mydisplayname) then

    -- Parse out test name for event log
    -- insert alerts
        select into myresellerid resellerid from tblksaccounts where
accountno = r_testrecord.accountno limit 1;
        if not exists (select 1 from tblksalerts where kstestssysid =
r_testrecord.kstestssysid
           and cleartime is null order by testtime desc limit 1) then
             insert into tblksalerts ( testtime, statusid, replytxt,
priority, accountno,
                 eventsource, eventid, replyval, kstestssysid, eventlog,
resellerid)
             values (x_lasttesttime, x_laststatusid, x_lastreplytxt,
r_testrecord.priority,
                 r_testrecord.accountno, x_lasteventsource,
x_lasteventid, x_replyval,
                 r_testrecord.kstestssysid, x_eventlog, myresellerid);
        end if;

        select into alertsrecord ksalertssysid,
             testtime,
             statusid,
             cleartime,
             eventsource,
             lasteventsource,
             eventid,
             replyval,
             alertoccurrence
        from tblksalerts
        where kstestssysid = r_testrecord.kstestssysid
        order by testtime desc
        limit 1;

        mytrend := null;
        if x_replyval > alertsrecord.replyval then
            mytrend := 'U';
        elsif x_replyval < alertsrecord.replyval then
           mytrend := 'D';
        end if;

        -- calculate alert occurrence for Event Logs
        if x_eventlog is not null then
                if alertsrecord.alertoccurrence is null then
                    testcount = x_counter;
                else
                    testcount = alertsrecord.alertoccurrence+x_counter;
                end if;
        else
            testcount = 0;
        end if;

        oldstatusid = alertsrecord.statusid;
        if oldstatusid = 99 and x_statusid=8 then
           oldstatusid = 8;
        end if;

        if alertsrecord.cleartime is null then
            if  (x_cleartime is not null) and x_eventlog is null then
                update tblksalerts
                  set cleartime =  x_cleartime,
                      laststatusid = x_statusid,
                      lastreplytext = x_replytxt,
                      lasttesttime =  x_testtime,
                      lasteventsource = x_eventsource,
                      lasteventid = myeventid,
                      replyval = x_replyval,
                      trend = mytrend,
                      alertoccurrence = testcount
                where ksalertssysid = alertsrecord.ksalertssysid;
            else
                update tblksalerts
                  set laststatusid = x_statusid,
                    lastreplytext = x_replytxt,
                    lasttesttime =  x_testtime,
                    lasteventsource = x_eventsource,
                    lasteventid = myeventid,
                    replyval = x_replyval,
                    trend = mytrend,
                    alertoccurrence = testcount,
                    statusid = oldstatusid
                where ksalertssysid = alertsrecord.ksalertssysid;
            end if;
        end if;
    end if;

end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION fn_dtaalerts(character varying, timestamp without time
zone, character varying, integer, character varying, integer, real,
character varying, integer, timestamp without time zone, timestamp
without time zone, integer, character varying, character varying,
integer) OWNER TO postgres;

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, July 29, 2009 3:31 PM
> To: Alvaro Herrera
> Cc: Benjamin Krajmalnik; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Error in creating the backend query
>
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Benjamin Krajmalnik wrote:
> >> Please note the "lasteventsource is null" instead of
> "lasteventsource =
> >> null" which is being generated when the value of x_eventsource is
> null.
>
> > Do you have transform_null_equals set?
>
> Even if he did, that wouldn't affect the source form of the query.
> Insertion of explicit constant values and casts like that isn't
> something plpgsql would do on its own either.  My bet is that this has
> got nothing to do with plpgsql, and in fact the query was generated
> client-side using some rather ill-designed parameter substitution
code.
>
> If this can actually be reproduced in bare plpgsql, I would like to
see
> a complete test case.
>
>             regards, tom lane