PostgreSQL: how to catch exception in a function? -

i have table , 3 functions fntrans-calls->fntrans2-calls->fntrans3

in example have removed call of fntrans3();

after call

select public.fntrans2(); 

the table t2 contains records, ok, clear, rollback works savepoint inside of function

but when call select public.fntrans();

the table not contain rows , output shows notice exception in fntrans;

why in 2nd case exception throwed 1st function when call fntrans2 caught inside function.

create table t2(id serial primary key, f1 integer);  create or replace function "public"."fntrans" ( ) returns integer $body$ declare d double precision; begin raise notice 'fntrans';   insert t2 (f1) values (1);   insert t2 (f1) values (2);   insert t2 (f1) values (3);   select fntrans2();   insert t2 (f1) values (4);   return 1; exception     when others    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 $body$ declare d double precision; begin     raise notice 'fntrans2';     insert t2 (f1) values (10);     insert t2 (f1) values (22);     insert t2 (f1) values (30);      begin             raise exception 'oooooppsss 2!';             insert t2 (f1) values (40);             return 1;     exception        when others return 0;     end; end; $body$ language 'plpgsql' volatile;   create or replace function public.fntrans3 ( ) returns integer $body$ declare d double precision; begin    raise notice 'fntrans3';    insert t2 (f1) values (100);   insert t2 (f1) values (200);   insert t2 (f1) values (300);   raise exception 'oooooppsss 3!';   insert t2 (f1) values (400);   return 1; exception     when others return 0; end; $body$ language 'plpgsql' volatile called on null input security invoker cost 100; 

your problem line

select fntrans2(); 

in fntrans. cannot use select without into clause in pl/pgsql.

without exception block you'll following message:

context:  sql statement "select fntrans2()" pl/pgsql function fntrans() line 8 @ sql statement error:  query has no destination result data hint:  if want discard results of select, use perform instead. context:  pl/pgsql function fntrans() line 8 @ sql statement 

that pretty self-explanatory.
explains why don't see results function fntrans2 – doesn't called.

you change offending line to

perform fntrans2();