Postgres Pro
Downloads
Home   >   mailing lists

Thread: SYNTAX ERROR at or near SQLSTATE

SYNTAX ERROR at or near SQLSTATE

From
vishal saberwal
Date:
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:
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;


Re: SYNTAX ERROR at or near SQLSTATE

From
Michael Fuhr
Date:
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

Re: SYNTAX ERROR at or near SQLSTATE

From
vishal saberwal
Date:
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:
> 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