Catch all error psql function exception

exception-handling plpgsql psql
Pavan Ebbadi picture Pavan Ebbadi · Feb 2, 2017 · Viewed 15k times · Source

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

Answer

Pavel Stehule picture Pavel Stehule · Feb 2, 2017

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!

Related questions

Elegant way of handling PostgreSQL exceptions?

In PostgreSQL, I would like to create a safe-wrapping mechanism which returns empty result if an exception occurs. Consider the following: SELECT * FROM myschema.mytable; I could do the safe-wrapping in the client application: try { result = execute_query('SELECT value …

Read More
plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function

I have a plpgsql function in PostgreSQL 9.2 which returns a table. The function runs several SELECTs that return the same columns as the function and then either returns those results or raises an exception, depending on some checks. The only …

Read More
Catch multiple exceptions in one line (except block)

I know that I can do: try: # do something that may fail except: # do this if ANYTHING goes wrong I can also do this: try: # do something that may fail except IDontLikeYouException: # say please except YouAreTooShortException: # stand on a ladder …

Read More