Can I use EXCEPTIONs in a FOR LOOP to force continuation on error?

exception, loops, plpgsql, postgresql

Solution

Yes. You can put the payload in a separate code block with exception handling:

FOR temp_rec IN tlcursor LOOP
   tl2 := temp_rec; --the location to be updated
   --Do the Routing and UPDATE the taxilocs row.
   BEGIN
      UPDATE taxilocs20120113 
      SET    route = pgr_trsp (
      'SELECT * FROM th_2po_4pgr',
      tl1.map_id, tl1.map_pos, tl2.map_id, tl2.map_pos, false, true);
   EXCEPTION WHEN OTHERS THEN
      -- keep looping
   END;
    tl1 := tl2;
END LOOP;

There is an example in the manual.

But I fail to see why you assign `tl2` first (instead of `tl1`), which is bound to cause an exception at the first iteration of the loop. You can avoid the problem a priori by using a `FOR` loop instead of an explicit cursor, in combination with an enhanced query. See below.

Also, your `UPDATE` has no `WHERE` condition, which is almost certainly wrong.

And the function `pgr_trsp()` looks suspicious to say the least. Passing code as text reeks of SQL injection. See:

- Postgres functions vs prepared queries

Audited function in updated question

Rewriting your code to use set-based logic instead of looping might be cleaner and faster. For starters, you can simplify to something like this (still with a loop, but simplified):

CREATE OR REPLACE FUNCTION fm_seqrouting()
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
DECLARE 
   r record;
BEGIN
   FOR r IN 
      SELECT oid                                -- no proper pk?
           , th_2po_4pgr_id                     AS map_id1
           , th_2po_4pgr_position               AS map_pos1
           , lead(th_2po_4pgr_id)       OVER w  AS map_id2
           , lead(th_2po_4pgr_position) OVER w  AS map_pos2
           , count(*)                   OVER () AS ct
      FROM   testlocs
      WINDOW w AS (ORDER BY veh_id, dt)
      ORDER  BY veh_id, dt              -- don't need order by columns in result
   LOOP
      BEGIN -- may be unnecessary
         UPDATE taxilocs20120113 
         SET    "pgRoute" = pgr_trsp(
                   'SELECT * FROM th_2po_4pgr'
                 , r.last_map_id, r.last_map_pos, r.map_id, r.map_pos, false, true)
         WHERE  taxilocs20120113.oid = r.oid;
      EXCEPTION
         WHEN SQLSTATE '55000' THEN NULL;
         WHEN SQLSTATE 'XX000' THEN NULL;
         WHEN SQLSTATE '38001' THEN NULL;
      END;
   END LOOP;

   RETURN r.ct;
END
$func$;

In particular, using ...

- A `FOR` loop with implicit cursor instead of an (unwieldy) explicit cursor.

- Window functions.

Problem

Original source