Handling EXCEPTION and return result from function
13,965
Solution 1
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;
Solution 2
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.
Author by
Oto Shavadze
Updated on June 05, 2022Comments
-
Oto Shavadze almost 2 years
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?