Message-ID: <55A8B097.8000303@imap.cc>
Date: Fri, 17 Jul 2015 10:36:55 +0300
From: Alexey Bashtanov <bashtanov@imap.cc>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: Alex Ignatov <a.ignatov@postgrespro.ru>, pgsql-sql@postgresql.org
Subject: Re: User defined exceptions
References: <55A669CA.3070302@postgrespro.ru>
In-Reply-To: <55A669CA.3070302@postgrespro.ru>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql