title: Cannot catch Exception from Postgres plpgsql Procedure and Exception handling best practices?
body: <p>I have a plpgsql Procedure where I am simply trying to handle any possible exceptions, since I will be running these Procedures on pg_cron (automated) and I do not want anything to fail. The basic skeleton of the procedure looks like this:</p>
  <pre><code>CREATE OR REPLACE PROCEDURE marketing_offers.stackover_overflow_ex_question(limit_size integer)
      LANGUAGE plpgsql
  AS
  $procedure$
  DECLARE
      n_rec_cnt  bigint;
     
  BEGIN
          LOOP
              EXIT WHEN n_rec_cnt = 0;
            
             WITH cte AS (SELECT *
                          FROM master_table mt
                          where mt.created_date &lt;= '1999-01-01'::time
                          LIMIT limit_size)
              INSERT INTO some_archive_table (SELECT * FROM cte)
              COMMIT;
             
             GET DIAGNOSTICS n_rec_cnt = row_count;
         
             RAISE EXCEPTION 'Max retry count exceeded';
            
              begin
             EXCEPTION 
             WHEN OTHERS then
              GET STACKED DIAGNOSTICS text_var1 = message_text,
                            text_var2 = PG_EXCEPTION_DETAIL,
                            text_var3 = PG_EXCEPTION_HINT;
              RAISE NOTICE 'error msg is %', text_var1;
                
              UPDATE job_log
              SET error_msg  = text_var1
              return;
             end;
            
             
          END LOOP;      
  END;
  $procedure$
  ;
  </code></pre>
  <p>The problem is the RAISE NOTICE with <code>text_var1</code>, which is supposed to hold the SQL exception message, never gets logged neither does the <code>UPDATE</code> statement to my <strong>job_log</strong> table that should hold the message also.</p>
  <p>I also would like to add that , I had to surround the <code>EXCEPTION</code> block with another <code>begin</code> and <code>end</code> , because when I did not, I would get a syntax error.</p>
  <p>I am simply trying to catch the exception from my SQL script - should I be using a different EXCEPTION type and should I be looking for specific SQL codes? I'm kind of confused what the best practices are here</p>