All, 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 log_alert_out and log_alert_into values somehow need to be cleared manually? :
$BODY$ DECLARE log_alert_out boolean; log_alert_into boolean; BEGIN IF (TG_OP = 'INSERT') THEN SELECT INTO -- First grab the DISJOINT values for each end of line segment . . . log_alert_out, log_alert_into ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom), ST_DisJoint(ST_StartPoint(T.the_line), fences.the_geom) from loc T join stp_fences as fences on St_Crosses(T.the_line, fences.the_geom) order by T.acqtime desc -- This should be the last record inserted. limit 1 ; IF (log_alert_out <> log_alert_into) THEN -- If points are not both IN or OUT of polygon . . . we only want crossings . . . INSERT INTO boundary_alerts_log select T.recid, T.msgid, T.acqtime, T.vname, T.the_line, ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out, fences.code, fences.type from loc T join stp_fences as fences on St_Crosses(T.the_line, fences.the_geom) and (ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom) <> ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom)) order by T.acqtime desc -- This should be the last record inserted. limit 1 ; RETURN new; END IF; END IF; END; $BODY$ LOG extract: 10:34:06 DbSvcX: 'executeBat': Error during 'insertTable'. 10:34:06 DbSvcX: ERROR: duplicate key value violates unique constraint "boundary_alerts_log_pkey" Detail: Key (recid)=(4299314) already exists. Where: SQL statement "INSERT INTO boundary_alerts_log select T.recid, T.msgid, T.acqtime, T.vname, T.the_line, ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out, fences.code, fences.type from loc T join stp_fences as fences on St_Crosses(T.the_line, fences.the_geom) -- and -- (ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom) -- <> -- ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom)) order by T.acqtime desc limit 1" PL/pgSQL function "boundary_alerts" line 25 at SQL statement . . . . . . . ## some stuff removed . . . . . . . . . . . 10:34:06 DbSvcX: 'executeBat': Error during 'insertTable'. 10:34:06 DbSvcX: ERROR: duplicate key value violates unique constraint "boundary_alerts_log_pkey" Detail: Key (recid)=(4299314) already exists. Where: SQL statement "INSERT INTO boundary_alerts_log select T.recid, T.msgid, T.acqtime, T.vname, T.the_line, ST_DisJoint(ST_EndPoint(T.the_line), fences.the_geom) as out, fences.code, fences.type from loc T join stp_fences as fences on St_Crosses(T.the_line, fences.the_geom) -- and -- (ST_Disjoint(ST_EndPoint(T.the_line), fences.the_geom) -- <> -- ST_Disjoint(ST_StartPoint(T.the_line), fences.the_geom)) order by T.acqtime desc limit 1" PL/pgSQL function "boundary_alerts" line 25 at SQL statement 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'); 10:34:06 DbSvc: Loc: Inserted.
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users