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;
$$;