<html dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style id="owaParaStyle" type="text/css">P {margin-top:0;margin-bottom:0;}</style>
</head>
<body ocsi="0" fpstyle="1">
<div style="direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;">All,<br>
<br>
Is this looking correct (triggered AFTER insert) How does one test a "SELECT INTO" statement BTW??, this gets stuck on trying to insert the same RECID over and over again (error at the bottom . . .) do the
<font face="Courier New">log_alert_out</font> and <font face="Courier New">log_alert_into</font> values somehow need to be cleared manually? :<br>
<br>
<font face="Courier New">$BODY$<br>
DECLARE<br>
<br>
log_alert_out boolean;<br>
log_alert_into boolean;<br>
<br>
BEGIN<br>
IF (TG_OP = 'INSERT') THEN <br>
SELECT INTO </font><font face="Courier New"><font face="Courier New">-- First grab the DISJOINT values for each end of line segment . . .<br>
</font> log_alert_out, log_alert_into<br>
ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom), ST_DisJoint(ST_StartPoint(T.the_line), fences.the_geom)<br>
from<br>
loc T<br>
join<br>
stp_fences as fences<br>
on<br>
St_Crosses(T.the_line, fences.the_geom)<br>
order by <br>
T.acqtime desc -- This should be the last record inserted.<br>
limit<br>
1<br>
;<br>
<br>
IF (log_alert_out <> log_alert_into) THEN -- If points are not both IN or OUT of polygon . . . we only want crossings . . .<br>
INSERT INTO boundary_alerts_log<br>
select<br>
T.recid,<br>
T.msgid,<br>
T.acqtime,<br>
T.vname,<br>
T.the_line,<br>
ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out,<br>
fences.code,<br>
fences.type<br>
from<br>
loc T<br>
join<br>
stp_fences as fences<br>
on<br>
St_Crosses(T.the_line, fences.the_geom)<br>
and<br>
(ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom)<br>
<><br>
ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom))<br>
order by <br>
T.acqtime desc</font><font face="Courier New"><font face="Courier New"> -- This should be the last record inserted.</font><br>
limit<br>
1<br>
;<br>
RETURN new;<br>
END IF;<br>
END IF;<br>
END;<br>
$BODY$</font><br>
<br>
<br>
LOG extract:<br>
<br>
<font face="Courier New"><br>
10:34:06 DbSvcX: 'executeBat': Error during 'insertTable'.<br>
10:34:06 DbSvcX: ERROR: duplicate key value violates unique constraint "boundary_alerts_log_pkey"<br>
Detail: Key (recid)=(4299314) already exists.<br>
Where: SQL statement "INSERT INTO boundary_alerts_log<br>
select<br>
T.recid,<br>
T.msgid,<br>
T.acqtime,<br>
T.vname,<br>
T.the_line,<br>
ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out,<br>
fences.code,<br>
fences.type<br>
from<br>
loc T<br>
join<br>
stp_fences as fences<br>
on<br>
St_Crosses(T.the_line, fences.the_geom)<br>
-- and<br>
-- (ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom)<br>
-- <><br>
-- ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom))<br>
order by <br>
T.acqtime desc<br>
limit<br>
1"<br>
PL/pgSQL function "boundary_alerts" line 25 at SQL statement<br>
<br>
. . . . . . .<br>
## some stuff removed . . . .<br>
. . . . . . .<br>
<br>
10:34:06 DbSvcX: 'executeBat': Error during 'insertTable'.<br>
10:34:06 DbSvcX: ERROR: duplicate key value violates unique constraint "boundary_alerts_log_pkey"<br>
Detail: Key (recid)=(4299314) already exists.<br>
Where: SQL statement "INSERT INTO boundary_alerts_log<br>
select<br>
T.recid,<br>
T.msgid,<br>
T.acqtime,<br>
T.vname,<br>
T.the_line,<br>
ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out,<br>
fences.code,<br>
fences.type<br>
from<br>
loc T<br>
join<br>
stp_fences as fences<br>
on<br>
St_Crosses(T.the_line, fences.the_geom)<br>
-- and<br>
-- (ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom)<br>
-- <><br>
-- ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom))<br>
order by <br>
T.acqtime desc<br>
limit<br>
1"<br>
PL/pgSQL function "boundary_alerts" line 25 at SQL statement<br>
10:34:06 DbSvcX: INSERT INTO Loc(msgId,acqTime,can,esn,vName,lat,lon,course,alt,qual,vel,latPrv,lonPrv,coursePrv,velPrv,the_geom,the_geomPrv,the_line,msgType,msgIdRef,tName) VALUES (181210231034635,'2012-10-23 10:34:06',1850,'10202037','2687',44.9588,-93.1037,0.0,0.0,1,0.0,44.9587,-93.1037,0.0,0.0,'0101000020840D03002EE48CB7D0772141F734D5AC96AF0341','0101000020840D0300C345FCF7D0772141697B72FB72AE0341','0102000020840D030002000000C345FCF7D0772141697B72FB72AE03412EE48CB7D0772141F734D5AC96AF0341',18,0,'NONE');<br>
10:34:06 DbSvc: Loc: Inserted.<br>
<br>
<br>
</font><br>
</div>
</body>
</html>