SYNTAX ERROR at or near SQLSTATE

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(at)gmail(dot)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;
>
>