35

I am writing an function with exception catching and ignoring. I want to catch all the exceptions and just ignore it. Is there anyway to catch all the exceptions and not individually?

CREATE OR REPLACE FUNCTION ADD_TABLE_TO_ARCHIVE (a TEXT, b TEXT)
RETURNS INTEGER AS $SUCCESS$
DECLARE SUCCESS INTEGER;
BEGIN
    SUCCESS = 0;
    BEGIN
        UPDATE ARCHIVE_STATUS
        SET *****
        WHERE ***;
        SUCCESS = 1;
    EXCEPTION
        WHEN UNIQUE_VIOLATION 
        SUCCESS = 0;
    END;

   RETURN SUCCESS;
END;
$SUCCESS$ LANGUAGE plpgsql;

In place of unique exception, it should be any exception...

Fabio Bonfante
  • 5,128
  • 1
  • 32
  • 37
Pavan Ebbadi
  • 852
  • 1
  • 13
  • 26

1 Answers1

52

You can use EXCEPTION WHEN OTHERS clause:

BEGIN
  do something
EXCEPTION WHEN OTHERS THEN
  handle any exception
END;

Without some exception a using of this clause is not good idea. The debugging, issue diagnostics can be terrible when you use this pattern. It is strong feature (sometimes necessary), but dangerous!

Attention - the entry to, end leave of protected section has significant load. The overhead of savepoint and releasing of savepoint is not small. The overhead of this construct is significantly higher than on Oracle (although there is visual similarity, it does different things). Although this code looks like code in PL/SQL, the implementation is absolutely different. If you expect higher load of your application, use it carefully (not inside cycles, ...)

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • Would this type of exception lock the table? If so would disconnecting the session release the lock. – codeBarer Jun 03 '19 at 01:59
  • 1
    @codeBarer - exception by self locks nothing - some commands creates the locks on tables. Postgres releases the locks at transaction end time - handling exception is based on creating and releasing savepoints. What I know it has not any impact on locks. – Pavel Stehule Jun 03 '19 at 03:53
  • @PavelStehule how do you capture the error/exception message here and maybe log it to some table? – ennth Oct 08 '21 at 18:11
  • @ennth - the best way, is just sending it to Postgres log. Later specialized tools like splunk, can read it from Postgres log. Or you can read Postgres log, and fill some tables. Good design depends on size - for small application or application without large load, the tables as target can be good solution. In other cases special tools, special databases like influx or timescaledb can be much better solution. – Pavel Stehule Oct 09 '21 at 03:00