CODEWITHSUNDEEP
postgresqlexceptionplpgsqlpsql
Pavan Ebbadi
Pavan Ebbadi

Reputation: 932

PL/pgSQL function - Catch errors

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...

Upvotes: 42

Views: 40341

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45825

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, ...)

Upvotes: 64

Related Questions