Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZGEVI-0003RT-EC for pgsql-sql@arkaria.postgresql.org; Fri, 17 Jul 2015 22:53:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZGEVH-0000lG-V0 for pgsql-sql@arkaria.postgresql.org; Fri, 17 Jul 2015 22:53:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZG09v-00010P-Lq for pgsql-sql@postgresql.org; Fri, 17 Jul 2015 07:34:15 +0000 Received: from mail.ocslab.com ([195.91.155.110]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZG09t-0002qZ-K2 for pgsql-sql@postgresql.org; Fri, 17 Jul 2015 07:34:14 +0000 Received: by mail.ocslab.com (Postfix, from userid 99) id 179221405C8; Fri, 17 Jul 2015 10:34:12 +0300 (MSK) Message-ID: <55A8AFF3.7040305@ocslab.com> Date: Fri, 17 Jul 2015 10:34:11 +0300 From: Alexey Bashtanov User-Agent: Mozilla/5.0 (X11; Linux i686; rv:31.0) Gecko/20100101 Thunderbird/31.7.0 To: Alex Ignatov , pgsql-sql@postgresql.org Subject: Re: User defined exceptions References: <55A669CA.3070302@postgrespro.ru> In-Reply-To: <55A669CA.3070302@postgrespro.ru> Content-Type: multipart/alternative; boundary="------------020909040005030500010601" X-Pg-Spam-Score: -2.4 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------020909040005030500010601 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------020909040005030500010601 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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
--------------020909040005030500010601--