PROPOSAL - User's exception in PL/pgSQL
От | Pavel Stehule |
---|---|
Тема | PROPOSAL - User's exception in PL/pgSQL |
Дата | |
Msg-id | Pine.LNX.4.44.0506160954430.8754-100000@kix.fsv.cvut.cz обсуждение исходный текст |
Ответы |
Re: PROPOSAL - User's exception in PL/pgSQL
(Josh Berkus <josh@agliodbs.com>)
Re: PROPOSAL - User's exception in PL/pgSQL (Neil Conway <neilc@samurai.com>) |
Список | pgsql-hackers |
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 Everyexception's variable has unique SQLSTATEo 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 '02catch: %, %', sqlstate, sqlerrm::timestamp; end; begin raise exception division_by_zero 'testing'; exception when division_by_zerothen raise notice 'Divison by zero: %, %', sqlstate, sqlerrm; end; raise exception alias_div_by_zero 'Unhandledexception'; 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
В списке pgsql-hackers по дате отправления: