3

I have the following function for geocoding, it works fine without the EXCEPTION WHEN OTHERS THEN part. However, I want the loop continue in case if there is an exception. So I am trying to do this by using EXCEPTION WHEN OTHERS THEN

CREATE OR REPLACE FUNCTION mygetcounty2() RETURNS void LANGUAGE PLPGSQL AS $$
DECLARE idVariable uuid;
begin
FOR i IN 1..100 LOOP

SELECT id into idVariable FROM address_table WHERE county IS NULL AND parse_address_error = false LIMIT 1;

UPDATE address_table set county =
(select namelsad from tiger_data.county_all where ST_Contains( the_geom, ST_GeomFromText( (SELECT ST_AsText( ST_SnapToGrid(g.geomout, 0.00001) ) As wktlonlat FROM geocode(address, 1 ) AS g), 4269 ) ))
where id = idVariable;
EXCEPTION WHEN OTHERS THEN
     UPDATE address_table set parse_address_error = true where id = idVariable;
END LOOP;
return;
end;
$$;

But I am getting the following message when trying to create the function

ERROR:  syntax error at or near "EXCEPTION"
LINE 15: EXCEPTION WHEN OTHERS THEN
         ^
SQL state: 42601
Character: 511

Any idea what I am doing wrong?

EDIT:

I added a new begin, end block inside the loop. I no longer got an error when creating the function.

Create or replace function mygetcounty2()
returns void
language plpgsql
as
$$
DECLARE idVariable uuid;
begin
FOR i IN 1..100 LOOP

begin

SELECT id into idVariable FROM address_table WHERE county IS NULL AND parse_address_error = false LIMIT 1;

UPDATE address_table set county = 
(select namelsad from tiger_data.county_all where ST_Contains( the_geom, ST_GeomFromText( (SELECT ST_AsText( ST_SnapToGrid(g.geomout, 0.00001) ) As wktlonlat FROM geocode(address, 1 ) AS g), 4269 ) ))
where id = idVariable;
EXCEPTION WHEN OTHERS THEN
      raise notice 'oops %', sqlstate;
     UPDATE address_table set parse_address_error = true where  id = idVariable;

    end;
END LOOP;
return;
end;
$$;      

1 Answer 1

6

You cannot have an EXCEPTION clause in a LOOP - END LOOP block.

Wrap the first UPDATE in its own BEGIN - END block and add the clause there.

2
  • I'm not 100% sure what you meant, but I did look at another answer you had made with a exception block inside a loop. I added a new BEGIN and END block inside the loop. It did create the function, and seems to be working now. I have not tested it extensively yet. From the code I have in case of an error the update will occur and the loop will continue?
    – david
    Commented Nov 8, 2020 at 6:06
  • 1
    Yes, I meant exactly than. If the error is hit, execution will continue after the END, so it will continue to loop. You should indent your code properly, otherwise it is unmaintenable. Commented Nov 8, 2020 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.