4

This is my code

CREATE OR REPLACE FUNCTION test_excep (arg INTEGER) RETURNS INTEGER 
AS $$
    DECLARE res INTEGER;
    BEGIN
        res := 100 / arg;

        BEGIN
            EXCEPTION
            WHEN division_by_zero 
            THEN  RETURN 999;
        END;


        RETURN res;
    END;



$$
LANGUAGE plpgsql;

That is, I need returned "999", if happened division by zero, but this: SELECT test_excep(0)

returns error: division by zero CONTEXT: PL/pgSQL function test_excep(integer) line 4 at assignment

What is wrong in my code?

Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236

2 Answers2

6

The EXCEPTION clause needs to be in the same block as the exception.

For instance:

CREATE OR REPLACE FUNCTION test_excep (arg integer)
  RETURNS integer
AS
$func$
DECLARE
   res INTEGER;
BEGIN

res := 100 / arg;

RETURN res;

EXCEPTION
    WHEN division_by_zero 
    THEN  RETURN 999;

END
$func$
LANGUAGE plpgsql;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
5

Here is a correct function:

CREATE OR REPLACE FUNCTION test_excep (arg INTEGER) RETURNS INTEGER 
AS $$
    DECLARE res INTEGER;
    BEGIN
        res := 100 / arg;
        RETURN res;
    EXCEPTION
        WHEN division_by_zero 
        THEN  RETURN 999;
    END;
$$
LANGUAGE plpgsql;

EXCEPTION part must be inside the block where the exception is, and also must be the last part of the block.

StenSoft
  • 9,369
  • 25
  • 30
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44