[postgis-users] trigger function with nest IF/THEN, . . . . problems . . .

[Basques, Bob (CI-StPaul)]

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.



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121023/d4eb6a64/attachment.html>