CODEWITHSUNDEEP
postgresqlerror-handling
Luba Weissmann
Luba Weissmann

Reputation: 187

postgress error handling from function

I try to run function that runs other functions and capture errors, in case one or few of the functions will fail. The function goes like this (I removed unnecessary parts):

CREATE OR REPLACE FUNCTION func()
 RETURNS void
 LANGUAGE plpgsql
AS $function$

BEGIN
cur_time:=now();
    FOR r IN select func_num FROM mytable order by func_num
    LOOP

        select schema , function_name, last_run, period into _schema, _func_name,_last_run, _period  from mytable
       ;

        if _last_run+_period <=cur_time then perform _schema||'.'||_func_name||'()' ; end if;


        EXCEPTION WHEN OTHERS THEN

            RAISE NOTICE 'exception (code=%): %', SQLCODE, SQLERRM;           
            _error:=SQLCODE; 

            if _last_run+_period >=cur_time then update mytable set error=_error where schema=_schema and function_name=_func_name; end if;

    END LOOP;

    RETURN;

END;

I'm getting the following error: "ERROR: ERROR: syntax error at or near "EXCEPTION"" and I cannot find it :((

Will appreciate any help!

Upvotes: 0

Views: 278

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

If you read the relevant part of the manuals you will see you need BEGIN ... EXCEPTION

...
LOOP
  BEGIN
  ...
  EXCEPTION
  ...
  END;
END LOOP;

Upvotes: 1

Related Questions