PostgreSQL: how to catch exception in a function?
<p>I have a table and 3 functions 
fntrans-calls->fntrans2-calls->fntrans3</p>

<p>In this example I have removed call of fntrans3();</p>

<p>After this call</p>

<pre><code>SELECT public.fntrans2();
</code></pre>

<p>the table t2 contains records, ok, it is clear, rollback works to the savepoint inside of function</p>

<p>But when I do call
    SELECT public.fntrans();</p>

<p>the table does not contain any rows and output shows notice about exception in     fntrans;</p>

<p>Why in the 2nd case exception throwed to the 1st function but when I call fntrans2 only it caught inside this function.</p>

<pre><code>create table t2(ID SERIAL PRIMARY KEY, f1 INTeger);

CREATE OR REPLACE FUNCTION "public"."fntrans" (
)
RETURNS integer AS
$body$
declare d double precision;
BEGIN
raise notice 'fntrans';
  INSERT INTO t2 (f1) VALUES (1);
  INSERT INTO t2 (f1) VALUES (2);
  INSERT INTO t2 (f1) VALUES (3);
  select fntrans2();
  INSERT INTO t2 (f1) VALUES (4);
  RETURN 1;
EXCEPTION  
  WHEN OTHERS THEN 
  BEGIN 
    raise notice 'fntrans exception';
    RETURN 0;
  END;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;


CREATE OR REPLACE FUNCTION public.fntrans2 (
)
RETURNS integer AS
$body$
declare d double precision;
BEGIN
    raise notice 'fntrans2';
    INSERT INTO t2 (f1) VALUES (10);
    INSERT INTO t2 (f1) VALUES (22);
    INSERT INTO t2 (f1) VALUES (30);

    BEGIN
            raise exception 'Oooooppsss 2!';
            INSERT INTO t2 (f1) VALUES (40);
            RETURN 1;
    EXCEPTION   
    WHEN OTHERS THEN RETURN 0;
    END;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;


CREATE OR REPLACE FUNCTION public.fntrans3 (
)
RETURNS integer AS
$body$
declare d double precision;
BEGIN
   raise notice 'fntrans3';

  INSERT INTO t2 (f1) VALUES (100);
  INSERT INTO t2 (f1) VALUES (200);
  INSERT INTO t2 (f1) VALUES (300);
  raise exception 'Oooooppsss 3!';
  INSERT INTO t2 (f1) VALUES (400);
  RETURN 1;
EXCEPTION  
  WHEN OTHERS THEN RETURN 0;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
</code></pre>