CODEWITHSUNDEEP
postgresqlfunctionexception
ZedZip
ZedZip

Reputation: 6472

PostgreSQL: how to catch exception in a function?

I have a table and 3 functions fntrans-calls->fntrans2-calls->fntrans3

In this example I have removed call of fntrans3();

After this call

SELECT public.fntrans2();

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

But when I do call SELECT public.fntrans();

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

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

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;

Upvotes: 2

Views: 9913

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246443

Your problem is the line

select fntrans2();

in fntrans. You cannot use SELECT without an INTO clause in PL/pgSQL.

Without the EXCEPTION block you'll get the following message:

CONTEXT:  SQL statement "select fntrans2()"
PL/pgSQL function fntrans() line 8 at SQL statement
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function fntrans() line 8 at SQL statement

That is pretty self-explanatory.
It also explains why you don't see any results from the function fntrans2 – it doesn't get called.

You could change the offending line to

PERFORM fntrans2();

Upvotes: 5

Related Questions