PostgreSQL Discussion - SYNTAX ERROR at or near SQLSTATE

Author

SYNTAX ERROR at or near SQLSTATE

vishal saberwal

2006-01-26, 5:16 pm

hi,
I found the following function on
http://archives.free.net.ph/message...326e27.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_Han
dling_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_Han
dling_Function(varch
ar) 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;
>
>

Michael Fuhr

2006-01-26, 5:16 pm

On Thu, Jan 26, 2006 at 03:02:46PM -0800, vishal saberwal wrote:
> 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

SQLSTATE and SQLERRM are new in 8.1; they're not available in earlier
versions unless you've applied some patch.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

vishal saberwal

2006-01-26, 5:24 pm

oh thanks,
i didnt know that,

highly appreciate your help and quick response ...
vish

On 1/26/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Thu, Jan 26, 2006 at 03:02:46PM -0800, vishal saberwal wrote:
>
> SQLSTATE and SQLERRM are new in 8.1; they're not available in earlier
> versions unless you've applied some patch.
>
> --
> Michael Fuhr
>

Sponsored Links