On Wed, Ju= l 15, 2015 at 10:10 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

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

do $$
begin
=C2=A0=C2=A0 raise exception using errcode=3Dexception_aaa();
exception
=C2=A0=C2=A0 when=C2=A0 sqlstate exception_aaa()
=C2=A0=C2=A0 then
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 raise notice 'got exception %',s= qlstate;
end;
$$
=C2=A0
Got:

ERROR:=C2=A0 syntax error at or near "exception_aaa"
LINE 20: sqlstate exception_aaa()
=20
I looks like "when=C2=A0 sqlstate exception_aaa()" doesn'= t work.

How can I catch exception in this case?

=E2=80=8BI'm doubtful that it can be done presently.

<= /div>

If it were possible your exception_aaa function would have to be de= clared IMMUTABLE.=C2=A0 It also seems pointless to declare it security defi= ner.

=E2=80=8BThere is nothing in the documentation th= at suggests that (or, to be fair, prohibits) the "condition" can = be anything other than a pre-defined name or a constant string.=C2=A0 When = plpgsql get a function body it doesn't go looking for random functions = to execute.

David J.

--001a1132f2146cf10c051aeaae76-- From pavel.stehule@gmail.com Wed Jul 15 15:56:36 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZFP2y-0003K4-9l for pgsql-sql@arkaria.postgresql.org; Wed, 15 Jul 2015 15:56:36 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZFP2x-0000k4-S8 for pgsql-sql@arkaria.postgresql.org; Wed, 15 Jul 2015 15:56:35 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZFP21-00086Q-Ad for pgsql-sql@postgresql.org; Wed, 15 Jul 2015 15:55:37 +0000 Received: from mail-wi0-x229.google.com ([2a00:1450:400c:c05::229]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZFP1u-0007bt-89 for pgsql-sql@postgresql.org; Wed, 15 Jul 2015 15:55:36 +0000 Received: by widjy10 with SMTP id jy10so3903609wid.1 for ; Wed, 15 Jul 2015 08:55:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; bh=vYM3c+bwC3V6+HCKCNTNGkrx7122IMoXLUor3syRDSU=; b=yq13LCtVXH9Zgp5lT1QCq8pweUVcjdcOMWEmcciNJuieKyRTUCINl2Zz+6V1CEa76+ MUX6nLK0F5alH8zynKzrNrupNl4hvBdNI/7nLTBIoCqK4grYwaxoEAyLLUH2FgrY/JuE d+qRrDwXbmvPILmuZMun4YypKQyehfEbX4ng93x2BO7fnn34euaA45sT0KOWOvOZKhT5 7iuy+xZeS2OvGKAa1fwwAJiVpm+b5XFzkwl0jMEe0IP4bo5h1+y7qT6O+sB3kl+wmHkR 5HJRtZoe+OX/fDtfv3xjFcGvUKWpfF5XZDugiXm3nBskYhaYj/Txi52lwWngYlm6z+bc mvdA== X-Received: by 10.194.81.67 with SMTP id y3mr9565268wjx.7.1436975728146; Wed, 15 Jul 2015 08:55:28 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.90.85 with HTTP; Wed, 15 Jul 2015 08:54:48 -0700 (PDT) In-Reply-To: <55A669CA.3070302@postgrespro.ru> References: <55A669CA.3070302@postgrespro.ru> From: Pavel Stehule Date: Wed, 15 Jul 2015 17:54:48 +0200 Message-ID: Subject: Re: User defined exceptions To: Alex Ignatov Cc: postgres list Content-Type: multipart/alternative; boundary=047d7bdc865cd3bdb3051aebfb9a X-Pg-Spam-Score: -1.3 (-) 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 --047d7bdc865cd3bdb3051aebfb9a Content-Type: text/plain; charset=UTF-8 2015-07-15 16:10 GMT+02:00 Alex Ignatov : > 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? > this syntax is working only for builtin exceptions. PostgreSQL has not declared custom exceptions like SQL/PSM. You have to use own sqlcode and catch specific code. Regards Pavel > -- > Alex Ignatov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > > > > > ------------------------------ > [image: Avast logo] > > This email has been checked for viruses by Avast antivirus software. > www.avast.com > > --047d7bdc865cd3bdb3051aebfb9a Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

2015-07-15 16:10 GMT+02:00 Alex Ignatov <a.ignatov@postgrespr= o.ru>:

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

do $$
begin
=C2=A0=C2=A0 raise exception using errcode=3Dexception_aaa();
exception
=C2=A0=C2=A0 when=C2=A0 sqlstate exception_aaa()
=C2=A0=C2=A0 then
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 raise notice 'got exception %',s= qlstate;
end;
$$
=C2=A0
Got:

ERROR:=C2=A0 syntax error at or near "exception_aaa"
LINE 20: sqlstate exception_aaa()
=20
I looks like "when=C2=A0 sqlstate exception_aaa()" doesn'= t work.

How can I catch exception in this case?

thi= s syntax is working only for builtin exceptions. PostgreSQL has not declare= d custom exceptions like SQL/PSM.

You have to use own sql= code and catch specific code.

Regards

P= avel=C2=A0

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

This email has been checked for viruses by Avast antivirus software.
www.a= vast.com

--047d7bdc865cd3bdb3051aebfb9a-- From alexey_bashtanov@ocslab.com Fri Jul 17 22:53:16 2015 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-- From bashtanov@imap.cc Fri Jul 17 07:37:02 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZG0Cc-0006dP-Mu for pgsql-sql@arkaria.postgresql.org; Fri, 17 Jul 2015 07:37:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZG0Cb-00019Z-Uz for pgsql-sql@arkaria.postgresql.org; Fri, 17 Jul 2015 07:37:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZG0Cb-000190-6X for pgsql-sql@postgresql.org; Fri, 17 Jul 2015 07:37:01 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZG0CY-0002ER-87 for pgsql-sql@postgresql.org; Fri, 17 Jul 2015 07:36:59 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id DE22E20B2D for ; Fri, 17 Jul 2015 03:36:56 -0400 (EDT) Received: from frontend2 ([10.202.2.161]) by compute5.internal (MEProxy); Fri, 17 Jul 2015 03:36:56 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=imap.cc; h= content-transfer-encoding:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-sasl-enc :x-sasl-enc; s=mesmtp; bh=xEdyvdpG3xp6YcKXfH5B9RtshwU=; b=idH596 kz69sjfAcSzA1bScFFC1ONFFGqsZ40TDbROc/wpWX5JFNcPQt4aJqAJldeMxys8g qOipJ9BRJItHZ05DR0ywKveq5SOo8jB0/xrRH5k+rUUJNhJQzTYt80e13807pTmn JPI4G3Em52FDWWUvmtPIzgVY6fMWagdOufB8o= DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-sasl-enc:x-sasl-enc; s=smtpout; bh=xEdyvdpG3xp6YcK XfH5B9RtshwU=; b=f2Gjyt+bO9mY+wY3gGJxKaDcuigPxt7x5DzVelz1DRMACED xyKTXJwvzNEfSU+JkxvbcAF90VQml0WaRAbe98rlmcD9opPyqBe9oSjIc1ehZ1Yv yKzNY/qYLvZrmkP8erLPCQWUGjuLgCbtmSL+7GA24RxgVf0Bry0QkmFjF9WE= X-Sasl-enc: PzjVzZEavFrLpX/93SMxrmYtR3ymIIYIQxAJSqBq2GCd 1437118616 Received: from [192.168.1.23] (unknown [195.91.155.97]) by mail.messagingengine.com (Postfix) with ESMTPA id 56F0B680214; Fri, 17 Jul 2015 03:36:56 -0400 (EDT) Message-ID: <55A8B097.8000303@imap.cc> Date: Fri, 17 Jul 2015 10:36:55 +0300 From: Alexey Bashtanov User-Agent: Mozilla/5.0 (X11; Linux i686; rv:31.0) Gecko/20100101 Thunderbird/31.7.0 MIME-Version: 1.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: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.7 (--) 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 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 From a.ignatov@postgrespro.ru Fri Jul 17 15:32:46 2015 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZG7d0-0005q4-MF for pgsql-sql@arkaria.postgresql.org; Fri, 17 Jul 2015 15:32:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZG7cz-0001rn-VN for pgsql-sql@arkaria.postgresql.org; Fri, 17 Jul 2015 15:32:46 +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 1ZG7c0-0000mB-W6 for pgsql-sql@postgresql.org; Fri, 17 Jul 2015 15:31:45 +0000 Received: from newmail.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZG7bu-0003dl-Be for pgsql-sql@postgresql.org; Fri, 17 Jul 2015 15:31:44 +0000 Received: from [127.0.0.1] (unknown [192.168.27.1]) by mail.postgrespro.ru (Postfix) with ESMTPSA id A8F7221C2198 for ; Fri, 17 Jul 2015 18:31:37 +0300 (MSK) Message-ID: <55A91FDB.1010706@postgrespro.ru> Date: Fri, 17 Jul 2015 18:31:39 +0300 From: Alex Ignatov User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.7.0 MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: Re: User defined exceptions References: <55A669CA.3070302@postgrespro.ru> <55A8AFF3.7040305@ocslab.com> In-Reply-To: <55A8AFF3.7040305@ocslab.com> Content-Type: multipart/alternative; boundary="------------080103000301010107000508" X-Antivirus: avast! (VPS 150717-0, 17.07.2015), Outbound message X-Antivirus-Status: Clean X-Pg-Spam-Score: -3.1 (---) 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. --------------080103000301010107000508 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 17.07.2015 10:34, Alexey Bashtanov wrote: > 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 Yep already used this trick =) Anyway thank you! -- Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus --------------080103000301010107000508 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

On 17.07.2015 10:34, Alexey Bashtanov wrote:

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

Yep already used this trick =)
Anyway thank you!

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

--------------080103000301010107000508--