SYNTAX ERROR at or near SQLSTATE - Mailing list pgsql-general
| From | vishal saberwal |
|---|---|
| Subject | SYNTAX ERROR at or near SQLSTATE |
| Date | |
| Msg-id | 3e74dc250601261502g50def0a9q9f2cc772d7340702@mail.gmail.com Whole thread Raw |
| Responses |
Re: SYNTAX ERROR at or near SQLSTATE
|
| List | pgsql-general |
hi,
I found the following function on http://archives.free.net.ph/message/20050613.063258.1a326e27.en.html.
When i run it on PostgreSQL8.0.1 (on fedora core 2), i get the error:
dbm=# select * from excpt_test();
ERROR: syntax error at or near "sqlstate" at character 133
QUERY:
begin
begin
raise exception 'user exception';
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
begin
raise notice '% %', sqlstate, sqlerrm;
perform 10/0;
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end;
end;
CONTEXT: compile of PL/pgSQL function "excpt_test" near line 5
LINE 6: raise notice 'caught exception % %', sqlstate, sqlerrm;
^
dbm=#
Is there a some configuration parameter i need to set?
Function is as below:
create function excpt_test() returns void as $$
begin
begin
raise exception 'user exception';
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
begin
raise notice '% %', sqlstate, sqlerrm;
perform 10/0;
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end;
end; $$ language plpgsql;
Any help will be highly appreciated,
thanks,
vish
On 1/26/06, vishal saberwal <vishalsaberwal@gmail.com> wrote:
I found the following function on http://archives.free.net.ph/message/20050613.063258.1a326e27.en.html.
When i run it on PostgreSQL8.0.1 (on fedora core 2), i get the error:
dbm=# select * from excpt_test();
ERROR: syntax error at or near "sqlstate" at character 133
QUERY:
begin
begin
raise exception 'user exception';
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
begin
raise notice '% %', sqlstate, sqlerrm;
perform 10/0;
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end;
end;
CONTEXT: compile of PL/pgSQL function "excpt_test" near line 5
LINE 6: raise notice 'caught exception % %', sqlstate, sqlerrm;
^
dbm=#
Is there a some configuration parameter i need to set?
Function is as below:
create function excpt_test() returns void as $$
begin
begin
raise exception 'user exception';
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
begin
raise notice '% %', sqlstate, sqlerrm;
perform 10/0;
exception when others then
raise notice 'caught exception % %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end;
end; $$ language plpgsql;
Any help will be highly appreciated,
thanks,
vish
On 1/26/06, vishal saberwal <vishalsaberwal@gmail.com> wrote:
hi all,
I am using PostgreSQL 8.0.1 on Fedora core 2.
My goal is to create a common Exception handling stored function that takes Error Constant (as defined in Error Codes document: AppendixA) and raises a customized exception.
The problem is:
(a) How do i catch these Error Constants? I was unable to use SQLSTATE and SQLERRM, for somehow the database didnt understand them.
(b) How do i catch these from OTHERS exception and pass it to the Common Exception Handling function?
-- In SP fucntion, error could be in Inserts, divide by zero pr updates.
-- The errors could be because a table is locked, or some other reasons.
CREATE or replace SP(int) returns int as $$
DECLARE
res int;
BEGIN
res:=0;
insert into tbl values ('a','b','c');
res:=2/$1;
update tbl set colA='x' where colA='a';
return res;
EXCEPTION
WHEN OTHERS THEN
Common_Exception_Handling_Function(Error_Constant);
END;
$$ language plpgsql;
-- This common function will be called from EXCEPTION blocks of all Stored functions (around 300).
-- All error codes will be defined in this common function and will raise a customized Exception message.
CREATE or replace Common_Exception_Handling_Function(varchar) returns VOID as $$
BEGIN
if $1='DIVISION_BY_ZERO' then
RAISE EXCEPTION 'DIVISION BY ZERO';
elsif $1='SYNTAX_ERROR' then
RAISE EXCEPTION 'SYNTAX ERROR';
. . .
. . .
. . .
end if;
END;
$$ language plpgsql;
pgsql-general by date: