Hello
I did some work on implementation of user's exception.
Generally:
o add pseudotype EXCEPTION
DECLARE excpt EXCEPTION [= 'SQLSTATE']
o change RAISE stmt
RAISE error_level [excpt_var|sys_excpt_name] errmsg, ...
o change EXCEPTION
EXCEPTION WHEN excpt_var|sys_excpt_name THEN ...
Rules:
o User can specify SQLSTATE only from class 'U1'
o Default values for SQLSTATE usr excpt are from class 'U0'
o Every exception's variable has unique SQLSTATE
o User's exception or system's exception can be raised only with
level EXCEPTION
Any comments, notes?
Regards
Pavel Stehule
Regres test:
create function innerfx() returns integer as $$
declare my_excpt exception = 'U0001';
begin -- using msgtext as one param of exception
raise exception my_excpt '%', CURRENT_TIMESTAMP;
return 1;
end $$ language plpgsql;
psql:regres.sql:6: ERROR: Invalid class for SQLSTATE value 'U0001' for
user's exception.
HINT: Select any unoccupied value from class U1 which is reserved for
user's exception.
CONTEXT: compile of PL/pgSQL function "innerfx" near line 1
create function innerfx() returns integer as $$
declare
my_excpt exception = 'U1001';
my_sec_excpt exception = 'U1001';
begin -- using msgtext as one param of exception
raise exception my_excpt '%', CURRENT_TIMESTAMP;
return 1;
end $$ language plpgsql;
psql:regres.sql:15: ERROR: Invalid SQLSTATE value 'U1001' for user's
exception.
HINT: Select any unoccupied value from class U1 which is reserved for
user's exception.
CONTEXT: compile of PL/pgSQL function "innerfx" near line 3
create function innerfx() returns integer as $$
declare my_excpt exception = 'U1001';
begin -- using msgtext as one param of exception
raise exception my_excpt '%', CURRENT_TIMESTAMP;
return 1;
end $$ language plpgsql;
CREATE FUNCTION
create function outerfx() returns integer as $$
declare
my_excpt exception = 'U1001';
alias_div_by_zero exception = 'U1002';
my_excpt_def_sqlstate exception;
begin
begin
raise exception my_excpt_def_sqlstate 'foo';
exception when my_excpt_def_sqlstate then
raise notice '01 catch: %, %', sqlstate, sqlerrm;
end;
begin
raise notice '%', innerfx();
exception when my_excpt then
raise notice '02 catch: %, %', sqlstate, sqlerrm::timestamp;
end;
begin
raise exception division_by_zero 'testing';
exception when division_by_zero then
raise notice 'Divison by zero: %, %', sqlstate, sqlerrm;
end;
raise exception alias_div_by_zero 'Unhandled exception';
return 1;
end; $$ language plpgsql;
CREATE FUNCTION
select innerfx();
psql:regres.sql:50: ERROR: 2005-06-16 10:12:53.27408+02
DETAIL: User's exception/notice - sqlstate: U1001, name: my_excpt
HINT: from RAISE stmt on line 3
select outerfx();
psql:regres.sql:51: NOTICE: 01 catch: U0001, foo
psql:regres.sql:51: NOTICE: 02 catch: U1001, 2005-06-16 10:12:53.274656
psql:regres.sql:51: NOTICE: Divison by zero: 22012, testing
psql:regres.sql:51: ERROR: Unhandled exception
DETAIL: User's exception/notice - sqlstate: U1002, name:
alias_div_by_zero
HINT: from RAISE stmt on line 21
drop function outerfx();
DROP FUNCTION
drop function innerfx();
DROP FUNCTION
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
