I am trying to create the following procedure on postgresql
CREATE PROCEDURE run_all_scheduled_tasks() LANGUAGE plpgsql AS $$ DECLARE rec record; BEGIN FOR rec IN SELECT * FROM some_table LOOP -- Statement 1..2..3..4 EXCEPTION WHEN others THEN RAISE NOTICE 'Error Occured! Code: %, Messege:%', sqlstate, sqlerrm; ROLLBACK; COMMIT; END LOOP; END; $$;
I get the following error:
ERROR: syntax error at or near "EXCEPTION"
LINE 12: EXCEPTION
It works properly if I shift exception block outside the for loop.
What I want to achieve is that for one iteration of loop, either all statements from 1..4 should get executed or none of them. If an error occurs, I still want to preserve the changes made up till previous iteration and then carry on to next iteration by simply raising a notice for the error.
What is causing this error?