PostgreSQL 9.5: Exception handling

Question

I have table called employee with two columns, and have created two functions for insertion and updation operations. These two function will be called through another function which named as udf_3().

I want to do exception handling on the third function that is udf_3() which should give me the details of which function has error.

--Table: employee

create table employee
(
 id int,
 name varchar(10)
);

--Function 1: udf_1() used for insertion.

create or replace function udf_1()
returns void as
$body$
begin

        insert into employee values(1,'Mak');
end;
$body$
language plpgsql;

--Function 2: udf_2() used for updation.

create or replace function udf_2()
returns void as
$body$
begin

        update employee
        set a_id = 99
        where name = 'Mak';

end;
$body$
language plpgsql;

--Function 3: udf_3() used to call all above function.

create or replace function udf_3()
returns int as
$body$
begin
    perform udf_1();

    perform udf_2();

    return 0;

    exception 
    when others then
        RAISE INFO 'Error Name:%',SQLERRM;
        RAISE INFO 'Error State:%', SQLSTATE;
        return -1;

end;
$body$
language plpgsql;

--Function Calling:

select * from udf_3();

Exception:

INFO:  Error Name:column "a_id" of relation "employee" does not exist
INFO:  Error State:42703

Problem: I am able to get the exception BUT not able to get from which function i got exception.

Answer 1

According to the documentation

Within an exception handler, one may also retrieve information about the current exception by using the GET STACKED DIAGNOSTICS command

https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS

Example:

create or replace function udf_3()
returns int as
$body$
declare
    err_context text;
begin
    perform udf_1();

    perform udf_2();

    return 0;

    exception 
    when others then
        GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;
        RAISE INFO 'Error Name:%',SQLERRM;
        RAISE INFO 'Error State:%', SQLSTATE;
        RAISE INFO 'Error Context:%', err_context;
        return -1;

end;
$body$
language plpgsql;

will display the following:

INFO: Error Context:SQL: "SELECT udf_1()"

But this is just a textual representation of the error. Your logic should not rely on it. It is better to use custom error codes to handle exception logic (and raise meaningful exceptions in your functions that you can catch and handle later on).

---

UPDATE:

Another solution is to separate your code in different blocks for which you can catch exceptions separately. In this case you know from which block the exception was raised:

DO $$
BEGIN

    -- Block 1
    BEGIN
        -- any code that might raise an exception
        RAISE EXCEPTION 'Exception 1'; -- for example
    EXCEPTION 
    WHEN others THEN    
        RAISE INFO 'Caught in Block 1';
    END;

    -- Block 2
    BEGIN
        -- any code that might raise an exception
        RAISE EXCEPTION 'Exception 2'; -- for example
    EXCEPTION 
    WHEN others THEN    
        RAISE INFO 'Caught in Block 2';
    END;

END $$