1

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?

closed as off-topic by mustaccio, Colin 't Hart, MDCCL, Vérace, kevinsky Apr 16 at 12:09

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, MDCCL, Vérace, kevinsky
If this question can be reworded to fit the rules in the help center, please edit the question.

0

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.