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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, July 29, 2009 3:31 PM
> To: Alvaro Herrera
> Cc: Benjamin Krajmalnik; pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Error in creating the backend query
>
> Alvaro Herrera <alvherre(at)commandprompt(dot)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