2

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?

1
  • An exception block needs a begin
    – user1822
    Commented Apr 12, 2019 at 11:48

1 Answer 1

4

As stated above in the comment, what I was missing was another BEGIN enclosing the exception.

Updated code:

CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT * FROM some_table LOOP
        BEGIN
        -- Statement 1..2..3..4
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Error Occured! Code: %, Messege:%', sqlstate, sqlerrm;
                ROLLBACK;
        END; 
        COMMIT;     
    END LOOP;

END;
$$;

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