Re: User defined exceptions - Mailing list pgsql-sql

On 15.07.2015 17:10, Alex Ignatov wrote:

Hello all!
Trying to emulate "named" user defined exception with:
CREATE OR REPLACE FUNCTION exception_aaa ()  RETURNS text AS $body$
BEGIN
   return 31234;     
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

do $$
begin
   raise exception using errcode=exception_aaa();
exception
   when  sqlstate exception_aaa()
   then
      raise notice 'got exception %',sqlstate;
end;
$$
 
Got:

ERROR:  syntax error at or near "exception_aaa"
LINE 20: sqlstate exception_aaa()

I looks like "when  sqlstate exception_aaa()" doesn't work.

How can I catch exception in this case?

Hello Alex,

The following workaround could be used:

do $$
begin
   raise exception using errcode = exception_aaa();
exception
   when others then
      if sqlstate = exception_aaa() then
         raise notice 'got exception %',sqlstate;
      else
         raise; --reraise
      end if;
end;
$$

Not sure if its performance is the same as in simple exception catch, maybe it would degrade.

Best Regards,
   Alexey Bashtanov

-- 
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

This email has been checked for viruses by Avast antivirus software. www.avast.com